Tricky SQL queries|SQL interview questions|Data Analyst| Oracle database|SQLSERVER|DBMS
Script:
** Create purchase table
create table purchase(buyer_id int(10),listing_id int(10),Quantity int(10));
** Insert sample data into purchase table
insert into purchase values(101,5001,1);
insert into purchase values(101,6010,1);
insert into purchase values(150,6010,2);
insert into purchase values(189,5001,1);
insert into purchase values(101,5001,1);
Let's learn and grow together with SQL. Keep on learning and practicing.
#oracledatabase DBMS #sqlinterviewquestionsandanswers#groupbyclause#whereclause#orderbyclause#sql #aggregatefunctionsandscalarfunctions#referentialintegrity #foreignkey #whatisnormalization#normalization #normalizationkyahotahai#sqltips #sqlcheatsheet#sqlserver#windowfunctions#constraints#constraintsinsql#keysinsql
Пікірлер: 4
my solution : with cte as ( Select *,ROW_NUMBER()over(partition by listing_id order by buyer_id) as rn from Purchases ),cte2 as ( Select * ,case when buyer_id in (select buyer_id from cte where c.rn>rn) then 1 else null end as repit_customer from cte as c ) Select listing_id,count(listing_id),count(repit_customer) from cte2 group by listing_id
One request, Please help to update create, and insert commands in the description of the video so we can also test it on our PC.
@CodingGyanOfficial.
18 күн бұрын
Please checkout in the description box:) Thank you for watching 😊
WITH CTE AS ( SELECT BUYER_ID,LISTING_ID,COUNT(*) AS CNT FROM PURCHASE GROUP BY BUYER_ID,LISTING_ID) SELECT LISTING_ID,SUM(CNT) AS PURCHASES,SUM(IF(CNT>1,1,0)) AS REPURCHASES FROM CTE GROUP BY LISTING_ID