Leetcode Hard Problem 4 | User Purchase Platform | Complex SQL 11

[@komalpawar5124]

Thanks Ankit for Great explanation and logic !! Here's my try on this: ;with cte as( select case when STRING_AGG(platform,',')='mobile,desktop' then 'both' else STRING_AGG(platform,',') end as pf,spend_date,user_id,sum(amount) Total,count(distinct user_id ) cnt from spending group by spend_date,user_id ), cte2 as ( select * from cte union all select distinct 'both' as pf,spend_date,null as user_id, 0 as total,0 as cnt from spending ) select pf,spend_date, sum(total)totalamount,count(distinct user_id)totalusers from cte2 group by spend_date,pf order by 1 desc

[@KoushikT]

Your solution was spot on and simple.Thanks for the amazing questions. My approach is almost the same but a bit lengthier ************************* with A as ( select spend_date, platform, user_id, amount, count(platform) over(partition by spend_date, user_id) as c from spending ) , B as ( select spend_date, case when c > 1 then 'both' when c = 1 and platform = 'mobile' then 'mobile' when c = 1 and platform = 'desktop' then 'desktop' else platform end as new_platform, user_id, amount from A union select distinct spend_date, 'both' as new_platform, null as user_id, 0 as amount from A ) select spend_date, new_platform, sum(amount) as total_amount, count(distinct user_id) as no_of_users from B group by spend_date, new_platform order by spend_date, new_platform

[@ankitbansal6]

Thanks for posting 🙏

[@saurabhsoni1998]

@KoushikT It should be union all and not union. Because you're adding a dummy record in all the spend date the duplicate record will get filter out and you will get 100 as amount for the 1st date which should actually be 200.

[@sivasrimakurthi206]

what a tricky way to use a column in the SELECT but avoid including in the GROUP BY clause, very logical. wonderful trick and explanation is good. Thanks for the help. Oh well, it also tell that you can run MAX function on string as well ?? that is was not what I imagined, this query taught a lot of concepts in 1 single query :-)

[@ankitbansal6]

Glad it was helpful!

[@sakethchandrakolisetty9836]

great question, i liked the part where you have used the dummy record, which if you had not used would have required cross join between dates and platform and again a join with aggreated data so that all possible combinations would show up.

[@vinayakjain9652]

Thank you for another useful video! This is my try with spend_cte as ( select *, COUNT(platform) OVER(partition by spend_date, user_id order by user_id) as cnt, CASE WHEN COUNT(platform) OVER(partition by spend_date, user_id order by user_id)=2 THEN 'both' ELSE platform END as platform_new from spending) select user_id, spend_date, platform_new, SUM(amount) as total_amt, COUNT(DISTINCT(user_id)) as total_users from spend_cte group by user_id, spend_date, platform_new;

[@akp7-7]

nice solution, but we missed second both row for value for date 2019-07-02 in output

[@pinaakgoel2937]

select count(distinct user_id) as total_users, spend_date, case when count(distinct platform) = 2 then 'both' else max(platform) end as platfrom, sum(amount) as total_amount from spending group by user_id,spend_date union select 0,spend_date,'both',0 from spending group by spend_date having count(distinct user_id) = count(user_id) order by spend_date, total_users desc

[@saurabhsoni1998]

wow!!

[@gontlakowshik2345]

Hi Ankit sir, Correct me if I am wrong. You inserted dummy record only for both case. Consider if mobile user is missing then we need to insert dummy record for mobile for that particular date. In the same way we should do for desktop user also.

[@sreejitchakraborty6575]

If we have a record in both, no point inserting in mobile and destop, since its self explanatory

[@RohitKumar-zm3nw]

With cte as ( select user_id, spend_date ,sum(amount) as Amount ,count (case when platform='Mobile' then 1 end) as mobile , count (case when platform='desktop' then 1 end) as desktop from spending group by user_id, spend_date), cte1 as( Select * , case when mobile=1 AND desktop = 1 then 'both' end as status from cte) select spend_date, case when mobile=1 then 'mobile' when desktop=1 then 'desktop' else 'both' end , case when mobile = 1 or desktop=1 then 1 end , Amount from cte1 order by spend_date, Amount

[@faizan4712]

nice approach regarding nsertion of dummy record. here is my approach : WITH user_platform_usage AS ( SELECT spend_date, user_id, SUM(CASE WHEN platform = 'desktop' THEN 1 ELSE 0 END) AS desktop_count, SUM(CASE WHEN platform = 'mobile' THEN 1 ELSE 0 END) AS mobile_count, SUM(CASE WHEN platform = 'desktop' THEN amount ELSE 0 END) AS desktop_amount, SUM(CASE WHEN platform = 'mobile' THEN amount ELSE 0 END) AS mobile_amount FROM user_spend GROUP BY 1, 2 ), platform_summary AS ( SELECT spend_date, 'desktop' AS platform, SUM(desktop_amount) AS total_amount, COUNT(CASE WHEN desktop_count > 0 AND mobile_count = 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 UNION ALL SELECT spend_date, 'mobile' AS platform, SUM(mobile_amount) AS total_amount, COUNT(CASE WHEN mobile_count > 0 AND desktop_count = 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 UNION ALL SELECT spend_date, 'both' AS platform, SUM(desktop_amount + mobile_amount) AS total_amount, COUNT(CASE WHEN desktop_count > 0 AND mobile_count > 0 THEN 1 ELSE NULL END) AS total_users FROM user_platform_usage GROUP BY 1 ) SELECT spend_date, platform, total_amount, total_users FROM platform_summary ORDER BY 1, 2 desc

[@ls47295]

Thanks Ankit. Great Work!! -- solution but if the data is missing,then it will not show (means dummy record) select spend_date ,case when count(1) > 1 then 'both' else platform end as "platform" ,count(distinct user_id) as usr_cnt,sum(amount) as Amount_spent from spending group by spend_date,user_id

[@muhammadsuhailek4677]

this is giving error on "spending.platform"

[@anishchhabra6085]

Great approach Ankit, I have a doubt if we don't aggregate the platform column it is still giving the same answer in MySQL, is this happening only for this test case or this works in MySQL and not in SQL Server?

[@kunalkumar-hl6gv]

the moment you inserted the dummy record i got shocked , because i have never done this type of concept before

[@Vijay-nf3bd]

First five rows I tried using STRING_AGG function. Last record there are multiple things but yours dummy record idea is best ;WITH CTE AS ( SELECT spend_date,STRING_AGG(platform,',') 'platform',SUM(amount) 'amount',user_id FROM spending GROUP BY spend_date,user_id UNION ALL SELECT spend_date,'both',0,NULL FROM spending ) SELECT spend_date,CASE WHEN platform='mobile,desktop' THEN 'both' ELSE platform END platform,SUM(amount) 'total_amt',COUNT(DISTINCT user_id) 'total_users' FROM CTE GROUP BY spend_date,CASE WHEN platform='mobile,desktop' THEN 'both' ELSE platform END ORDER BY 1

[@ankitbansal6]

Good effort. Thanks for posting 👏

[@jashandeepsingh9797]

Great and Simple solution.

[@shekharagarwal1004]

Thanks Ankit - Max()/Min() is game changer here

[@MrAnkitbatham]

My solution ; with cte as (select * , count(1) over (partition by user_id ,spend_date) as 'ct' from spending) select * from ( select spend_date , platform , sum(amount) as 'total amount', count(distinct user_id) as 'total_users' from cte where ct=1 group by 1,2 union all select spend_date,'both' as 'platform', sum(amount) as 'total amount' , count(distinct user_id) as 'total_users' from cte where ct 1 group by 1) as x order by 1

[@SudhirKumar-rl4wt]

Thanks for the question and solution.

[@ankitbansal6]

Welcome

[@tunguyenanh3071]

what an awsome answer! amazing good job and thank you

[@harishkanta3711]

Ankit bhai have learnt alot from you with time, i feel the answer to the question is not justified by the sql query thats shown in video. Below one, makes sense to me, please let me if am right: with cte as (select spend_date,count(1) as total_users_per_date,sum(amount) as total_amount_per_date_from_both from spending group by spend_date), cte2 as (select spend_date,sum(case when platform = 'mobile' then 1 else 0 end) as mobile_user, sum(case when platform = 'mobile' then amount else 0 end) as mobile_amount, sum(case when platform = 'desktop' then 1 else 0 end) as desktop_user, sum(case when platform = 'desktop' then amount else 0 end) as desktop_amount from spending group by spend_date) select cte.spend_date,total_users_per_date,total_amount_per_date_from_both,mobile_user,mobile_amount, desktop_user,desktop_amount from cte join cte2 on cte.spend_date= cte2.spend_date

[@oorjamathur8459]

Hi Ankit, with cte1 as (select spend_date, user_id, case when string_agg(platform,',') = 'mobile,desktop' then 'both' else STRING_AGG(platform,',') end as platforms, sum(amount) as amount1 from spending group by spend_date, user_id) , cte2 as (select spend_date, platforms, count(1) as no_of_users, sum(amount1) as total_amount from cte1 group by spend_date, platforms) , cte3 as (select * from (select distinct spend_date from cte2) as tbl1 cross join (select distinct platforms from cte2) as tbl2) select cte3.spend_date, cte3.platforms, isnull(no_of_users,0) as no_of_users, isnull(total_amount, 0) as total_amount from cte3 left join cte2 on cte3.spend_date = cte2.spend_date and cte3.platforms = cte2.platforms; This is how I solved it!

[@gunjanoberoi7887]

I got output from simple approach Select user_id, string_agg(platform,’ , ‘) as platform, spend_date, Count(distinct user_id) as total_no_users, sum(amount) as total_amt, from spending where platform =‘desktop’ or platform =‘mobile’ group by spend_date, user_id

[@abhijeetjain8228]

tricky question. Thanks Ankit

[@sai_sh]

with cte1 as( select count(distinct user_id) as total_users,spend_date,sum(amount) as amt , group_concat(platform) as plat from spending group by user_id , spend_date) select total_users, spend_date , case when plat = "desktop" then "desktop" when plat = "mobile" then "mobile" when plat ="mobile,desktop" then "both" end as platform from cte1 order by spend_date asc , platform desc

[@gauravgupta5530]

with cte as ( select spend_date, user_id, sum(amount) as total, group_concat(platform) as grouped from spending group by spend_date, user_id ) , cte2 as ( select spend_date, CASE WHEN grouped='mobile,desktop' or grouped='desktop,mobile' THEN "both" ELSE grouped END as platform, total as total_amount from cte ) select spend_date, platform, sum(total_amount) as total, count(*) as user from cte2 group by spend_date, platform; your teaching!

[@bukunmiadebanjo9684]

Had to take a cue from you to derive the last row. Here is my approach which solve the question before consulting your solution for the dummy entries with platform_track as (select user_id, spend_date, count(distinct platform) as no_of_platform_used from spending group by user_id, spend_date) ,platform_sub as (select sp.spend_date, sp.user_id, sp.platform, sp.amount ,case when pl.no_of_platform_used > 1 then 'both' else sp.platform end as platform_used from spending sp left join platform_track pl on pl.user_id = sp.user_id and pl.spend_date = sp.spend_date union all select distinct spend_date, null as user_id, null as platform, 0 as amount, 'both' as platform_used from spending) select spend_date, platform_used , sum(amount) as total_amount , count(distinct user_id) as total_users from platform_sub group by platform_used, spend_date order by spend_date, platform_used desc

[@SudhirKumar-rl4wt]

for first 5 rows ..dummy row idea didn't click :) with temp as ( select user_id,spend_date,platform,amount,count(1) over(partition by spend_date,user_id) cnt from spending ), temp1 as ( select spend_date,case when cnt = 2 then 'both' else platform end as platform,amount,user_id from temp ) select spend_date,platform,sum(amount),count(distinct user_id) from temp1 group by spend_date,platform

[@khushboobansal2312]

I have tried using union all but not inserted dummy record which we can replicate same process mentioned by Ankit: with cte as ( Select user_id from ( Select *, row_number() over (partition by spending.spend_date,spending.user_id) as rn from spending) where rn = 2) Select platform,spend_date,sum(amount) as total_amount,count(user_id) as total_users from spending where platform = 'mobile' and user_id not in (select user_id from cte) group by 1,2 union all Select platform,spend_date,sum(amount) as total_amount,count(user_id) as total_users from spending where platform = 'desktop' and user_id not in (select user_id from cte) group by 1,2 union all Select 'both' as platform,spend_date,sum(amount) as total_amount,count(user_id) as total_users from spending where user_id in (select user_id from cte) group by 1,2

[@saurabhkarambelkar4538]

with cte as(select *,count(distinct case when platform = 'mobile' then user_id else user_id end) as count,count( case when platform = 'mobile' then user_id else user_id end) as count1,sum(case when platform = 'mobile' then amount else amount end) as amount1 from spending group by user_id,spend_date order by spend_date) select spend_date,count,amount1,case when count1 = 2 then 'both' else platform end as platform1 from cte my approach to the sum I was not able to add the dummy column

[@Alexpudow]

Hi, thanks a lot for your tasks. This is my approach with join select a.spend_date, 'both' platform, sum(case when a.user_id=b.User_id then b.amount else 0 end) total_amount, count(distinct b.user_id) total_users from spending a left join spending b on a.user_id=b.user_id and a.spend_date=b.spend_date and a.platformb.platform group by a.spend_date union select a.spend_date, a.platform, sum(a.amount) total_amount, count(a.user_id) total_users from spending a left join spending b on a.user_id=b.user_id and a.spend_date=b.spend_date and a.platformb.platform where b.user_id is null group by a.spend_date, a.platform order by 1,2 desc

[@arthurmorgan9010]

Hello sir. Hope you are doing well. Could get to this far only :( with cte as ( select top 100 percent spend_date,platform,user_id,amount from spending group by spend_date,platform,user_id,amount order by spend_date,user_id ) ,cteone as ( select top 100 percent *,count(user_id) over(partition by user_id,spend_date order by spend_date) as valu from cte order by spend_date,user_id,amount ) ,ctetwo as ( select *,sum(amount) over(partition by user_id,spend_date order by spend_date) as amounts from cteone ) ,ctethree as ( select distinct spend_date, case when valu >=2 then 'both' when valu = 1 then platform else platform end as status,amounts,valu from ctetwo ) ,ctefour as ( select spend_date,status as 'platform',amounts as 'total_amount',count(valu) as total_users from ctethree group by spend_date,status,amounts,valu ) ,ctefive as ( select * from ctefour ) select * from ctefive

[@LaloSalamanca783]

with cte1 as (select *,count( platform) over (partition by spend_Date,user_Id) as r1 from spending) select * from ( select spend_Date,platform,sum(amount),count(distinct user_id) from cte1 where r1=1 group by 1,2 union all select spend_Date,'both' as platform,sum(amount),count(distinct user_id) from cte1 where r1=2 group by 1,2) b

[@Datapassenger_prashant]

ankit sir, main instructions hi nahi samajh paya islye aage nahi badha.. please help: kyunnki, on '2019-07-01' date mobile platform se 2 purchase hui hain.. aur aap ek bata rahe ho..

[@2412_Sujoy_Das]

Was finally able to partially complete it all because I felt that the dummy part would not be considered.............😣😣

[@suriyas6338]

Hi Ankit My Solution :) with cte1 as ( select distinct user_id, spend_date, PLATFORM, sum(amount) over(partition by user_id, spend_date order by spend_date) as total_amount, count(spend_date) over(partition by user_id, spend_date order by spend_date )as total_purchase from spending union select null as user_id, spend_date, 'both' as PLATFORM, 0 as total_amount, 0 as total_purchase from spending ) select distinct spend_date, case when total_purchase = 1 then PLATFORM when total_purchase = 2 then 'both' else PLATFORM end as platform, max(total_amount) over(), count(user_id) over(partition by spend_date, case when total_purchase = 1 then PLATFORM when total_purchase = 2 then 'both' else PLATFORM end order by spend_date) as total_users from cte1

[@rajshekar401]

Ankit, when we have only mobile platform record for one spend_date in the Spending table, like this insert into spending values (1,TO_DATE('2019-07-03','YYYY-MM-DD'),'mobile',100); then in the output we are getting two records for Spend_date = '2019-07-03' they are 'mobile' and 'both' platform but not 'Desktop' platform. how to get 'dummy 'Desktop' record in the output?

[@ankitbansal6]

You can add dummy records for mobile and desktop with 2 more unions like we did for both category.

[@shekharagarwal1004]

@raj shekhar : Insert the same dummy rec for Desktop and mobile : with all_spend as ( Select spend_date, user_id , max(PLATFORM) as platform ,count(1) as total_users , sum(amount) as Total_amount from Spending group by spend_date, user_id Having count(distinct platform) = 1 union Select spend_date, user_id , 'Both' as platform ,count(distinct user_id) as total_users , sum(amount) as Total_amount from Spending group by spend_date, user_id Having count(user_id) = 2 union Select distinct spend_date,null as user_id ,'Both' as platform ,0 as amount , 0 as total_users from Spending union Select distinct spend_date,null as user_id ,'mobile' as platform ,0 as amount , 0 as total_users from Spending union Select distinct spend_date,null as user_id ,'desktop' as platform ,0 as amount , 0 as total_users from Spending ) Select spend_date , platform , sum(Total_amount) as amount, count(distinct user_id) as total_users from all_spend group by spend_date , platform order by spend_date, platform

[@ajayk9605]

You added null value for both platform having 0 order id. But what if any one platform let's say mobile or laptop does not have any value than it will not return that platform on that date

[@satyabharadwaj7779]

will the below code work for this problem? select spend_date, sum(case when platform = "desktop" then total else 0 end) as "desktop only",sum(desktop_users) as "desktop users", sum(case when platform = "mobile" then total else 0 end) as "mobile only",sum(mobile_users) as "mobile users", sum(total) as both from ( select *, sum(amount) as total,count(*) as users,sum(case when platform = "mobile" then 1 else 0 end) as mobile_users,sum(case when platform = "desktop" then 1 else 0 end) as desktop_users from spending group by spend_date,platform ) s group by s.spend_date

[@thanipattavan]

By the qiestion i thoight they eant output like Date, mobile_sales, desktop-sales, both_sales..

[@redwannabil8031]

a little confusion here.. on 2019-07-01 the resultant total_users should be 2....becusase on that date two users used mobile platform

[@pavanreddy5724]

with cte as ( select user_id :: varchar,spend_date ,listagg(platform, ',') within group( order by spend_date) as usage , sum(amount) as toatal_amount , case when usage = 'mobile,desktop' then 'both' else usage end as platform from spending group by 1,2 union all select distinct null as user_id,spend_date,'' as usage,0 as total,'both' as platform from spending ) select spend_date,platform,sum(toatal_amount)toatal_amount,count(user_id) as total_users from cte group by 1,2 ;

[@subhojitchatterjee6312]

Bringing the last row is tricky and I could not do it. Here's my approach in MYSQL WITH CTE AS( SELECT *,COUNT(*) OVER(PARTITION BY USER_ID,SPEND_DATE) AS T1 FROM SPENDING ), NEW_DETAILS AS (SELECT *,CASE WHEN T1=2 THEN "BOTH" ELSE PLATFORM END AS NEW_PLATFORM FROM CTE) SELECT SPEND_DATE,NEW_PLATFORM AS PLATFORM,SUM(AMOUNT) AS TOTAL_AMOUNT,COUNT( DISTINCT USER_ID) AS TOTAL_USERS FROM NEW_DETAILS GROUP BY 1,2 ORDER BY 1,2 DESC;

[@yashsaxena7754]

How about this as a solution? A bit of hard coding at the end. with cte as (select spend_date,string_agg(platform,',') as platforms_used,sum(amount) as total_spend,count(distinct user_id) as total_users from spending group by spend_date,user_id order by spend_date,user_id) (select spend_date,(case when platforms_used='mobile,desktop' then 'both' else platforms_used end) as platforms_used,total_spend,total_users from cte) UNION select distinct spend_date,'both' as platforms_used, 0 as total_spend, 0 as total_users from spending where spend_date = '2019-07-02' order by spend_date,platforms_used desc

[@florincopaci6821]

Thank you for another useful video!

[@ankitbansal6]

🙏🙏

[@florincopaci6821]

@@ankitbansal6 1384 -total sales by year is also a very tricky question. All the best!

[@ashoktheking4871]

2019-07-01 there are 3 purchases so user_id 1,3 on desktop and user_id 1 on mobile then total users and amount should be different

[@anshumansrivastava2801]

Here is my approach to the problem:- with main_query as( select distinct a.* from( select userid,spenddate,sum(amount) over (partition by userid,spenddate) end as total_amount,count(user_id) over (partition by userid,spenddate) emp_cnt, case when emp_cnt=2 then 'both' else platform end as platform from users) a union all select null as userid, distinct spenddate, 0 as total_amount, 0 as emp_cnt, 'both' as platform from users ) select spend_date,platform,sum(total_amount) as total_amt, count(user_id) as total_users from main_query groupby spend_date,platform;

[@tanmaykumar3250]

You just solved this by introducing a trick but what if we have a day where there is no sales using mobile as well so we need put another dummy row here ? is it a good practice or is there any other way to do this ?

[@AnkitGupta-tp3ln]

I had the same question, I came up with this solution, I tried removing this record (3,'2019-07-02','desktop',100) . WITH items as( select distinct 'mobile' as platform, spend_date from spending union select distinct 'desktop' as platform, spend_date from spending union select distinct 'both' as platform, spend_date from spending), LIST_AGG AS( select CASE WHEN COUNT(DISTINCT PLATFORM)=2 THEN 'both' ELSE MAX(PLATFORM) END AS PLATFORM,user_id,spend_date,sum(amount) as amt from spending group by 2,3) select a.spend_date,a.platform,IFNULL(sum(amt),0) AS TOTAL_AMOUNT,count(USER_ID) TOTAL_USERS from items a left join list_Agg b on a.spend_date=b.spend_date and a.platform = b.platform group by 1,2 ORDER BY 1,2 DESC;

[@ganesh480555]

I got the same doubt it can be solved by taking a cross join of distinct dates and distinct platforms so that we will have entries for every day and we can do left join with all_spend table

[@gowdaharish3370]

without max also it will end up with the same result, right?

[@ankitbansal6]

Since the column is not in group by, you need to use any aggregation function.

[@anishchhabra6085]

Solved the question but it is too long but this is only thing I could think of on my own, I solved this without looking in the video with output_format as ( select distinct spend_date, platform from spending union all select distinct spend_date, 'both' as platform from spending order by spend_date asc, platform desc ), cte as ( select *, count(*) as purchased_from_both from spending group by spend_date, user_id ), final as ( select s.*, case when purchased_from_both = 2 then 'both' when purchased_from_both = 1 then s.platform end as final_platform from cte join spending s on s.user_id = cte.user_id and s.spend_date = cte.spend_date ) select o.spend_date, o.platform,count(distinct user_id) as total_users,ifnull(sum(amount),0) as total_amount from final f right join output_format o on o.spend_date = f.spend_date and o.platform = f.final_platform group by o.spend_date, o.platform order by spend_date asc, o.platform desc;

[@AbhishekKumar_02]

but this solution is not working on leetcode, only sample test case passed. 1/11 test case passed

[@shubhamagrawal7068]

A more simpler approach might be this : - with cte1 as ( select user_id, spend_date, group_concat(platform separator ',') platform, sum(amount) amount from spending group by 1,2) select spend_date, if(platform = 'mobile,desktop','both',platform) platform, sum(amount) total_amount, count(user_id) total_users from cte1 group by 1,2 union select distinct spend_date, 'both' platform, 0 total_amount, 0 total_users from spending where spend_date not in (select spend_date from cte1 where platform = 'mobile,desktop') Plz verify @Ankit Bansal

[@rahulmehla2014]

my approach: with cte as( select *,count(*) over(partition by spend_date,user_id) as cnt from spending), cte2 as( select max(case when cnt>1 then "both" else platform end) as platform,spend_date, sum(amount) as total_amount, count(distinct(user_id)) as total_users,cnt from cte group by spend_Date,user_id order by spend_date,total_amount), cte3 as( select * from cte2 union all select distinct 'both' as pf,spend_date,0 as total,0 as user_id,0 as cnt from spending) select spend_Date,platform,sum(total_amount) as total_amount,sum(total_users) as total_users from cte3 group by spend_Date,platform

[@praveenkumarrai101]

It took me 2 hours to understand, is this the same happening to many guys or just me?

[@SS-lt1nf]

This is so cool, Ankit.

[@ankitbansal6]

Thanks 🙏

[@rajunaik8803]

*without last entry* select spend_date,user_id,min(platform) platform,sum(amount) total_amount,count(distinct user_id) no_of_users from spending group by spend_date,user_id having count(distinct platform)=1 union all select spend_date,user_id,'both' platform,sum(amount) total_amount,count(distinct user_id) no_of_users from spending group by spend_date,user_id having count(distinct platform)=2 order by spend_date,platform desc

[@sreesennu]

what if july 2nd mobile was not purchased?

[@mayurbhat9479]

Started seeing your videos a month ago, and I am totally in love with the concepts you bring. Thank You. Here is my solution WITH CTE1 AS( SELECT spend_date, SUM(amount) as Amount, COUNT(DISTINCT user_id) as Users_Count, CASE WHEN COUNT(user_id)>1 THEN 'Both' ELSE MAX(platform) END AS PlatformName FROM spending GROUP BY user_id, spend_date UNION SELECT spend_date, 0 as Amount, 0 as Users_Count,'Both' AS PlatformName FROM spending GROUP BY user_id, spend_date) SELECT spend_date, SUM(Amount) as Amount, SUM(Users_Count) AS UsersCount, PlatformName FROM CTE1 GROUP BY spend_date, PlatformName

[@ankitbansal6]

Good effort 👍

[@adwaitbangale4963]

with cte as( select spend_date,user_id,case when count(platform)=2 then 'both' else max(platform) end as platform,count(distinct user_id ) user_count,sum(amount) total_amount from spending group by spend_date,user_id union all select distinct spend_date,null user_id, 'both' platform , 0 user_count, 0 total_amount from spending) select spend_date,platform,sum(user_count) user_count,sum(total_amount) total_amount from cte group by spend_date,platform order by spend_date,platform desc

[@amitjn7042]

What is wrong with my approach? SELECT spend_date, platform , count(DISTINCT(user_id)) as total_users , sum(amount) as total_amount from spending GROUP by spend_date, platform union all SELECT spend_date, 'both' as platform, count(DISTINCT(user_id)), sum(amount) from spending GROUP by spend_date ORDER by spend_date

[@growwithsandip6545]

Suppose, in a day only a single customer bought an item via Desktop. Then we will have no records for the "Mobile" platform, but we will have records for "Both" platform. which I think is not correct. check with the following table. create table spending (user_id int, spend_date date, platform varchar(10), amount int); insert into spending values (1,date '2019-07-01','mobile',100); insert into spending values (1,date '2019-07-01','desktop',100); insert into spending values (2,date '2019-07-01','mobile',100); insert into spending values (2,date '2019-07-02','mobile',100); insert into spending values (3,date '2019-07-01','desktop',100); insert into spending values (3,date '2019-07-02','desktop',100); insert into spending values (4,date '2019-07-03','mobile',100); insert into spending values (4,date '2019-07-04','desktop',100);

[@shekharagarwal1004]

@Grow with Sandip PFB the results : Select * From spending order by spend_date, user_id ; with all_spend as ( Select spend_date, user_id , max(PLATFORM) as platform ,count(1) as total_users , sum(amount) as Total_amount from Spending group by spend_date, user_id Having count(distinct platform) = 1 union Select spend_date, user_id , 'Both' as platform ,count(distinct user_id) as total_users , sum(amount) as Total_amount from Spending group by spend_date, user_id Having count(distinct platform) = 2 union Select distinct spend_date,null as user_id ,'Both' as platform ,0 as amount , 0 as total_users from Spending union Select distinct spend_date,null as user_id ,'mobile' as platform ,0 as amount , 0 as total_users from Spending union Select distinct spend_date,null as user_id ,'desktop' as platform ,0 as amount , 0 as total_users from Spending ) Select spend_date , platform , sum(Total_amount) as amount, count(distinct user_id) as total_users from all_spend group by spend_date , platform order by spend_date, platform

[@sreejitchakraborty1374]

In this case the 'both' will be 0 and the desktop will be 1. This automatically implies that mobile is 0. But yes from a solution point of view, it should be handled.

[@kanchidoshi6907]

This is my solution but not able to insert the last dummy record. - Getting lag and lead of the platform by spend date, user id and platform in desc order - Checking if mobile=desktop (from lead fn) and desktop = mobile (from lag fn) then consider as 'both' else show platform value itself select spend_date,platform_1 as platform, sum(amount) as amount,count(distinct user_id) from (select spend_date,user_id,platform,lead_pl,lag_pl,amount, case when platform like 'mobile' and lead_pl like 'desktop' then 'both' when platform like 'desktop' and lag_pl like 'mobile' then 'both' else platform end as platform_1 from (select spend_date,user_id,platform,amount, lead(platform) over(partition by spend_date,user_id order by platform desc) as lead_pl, lag(platform) over(partition by spend_date,user_id order by platform desc) as lag_pl from spending)x)y group by spend_date,platform_1

[@venkataramanakumar365]

with cte1 as ( select spend_date,platform,user_id,amount,count(1) over(partition by spend_date,user_id) rn from spending ), cte2 as ( select spend_date,platform,sum(amount) total_amount,count(1) total_users from cte1 where rn=1 group by spend_date,platform union all select spend_date,'both' as platform,sum(amount) total_amount,count(distinct user_id) total_users from cte1 where rn=2 group by spend_date ), cte3 as (select spend_date,'both' platform,0 total_amount,0 total_users from cte2 group by spend_date having count(1)!=3) select * from cte2 union all select * from cte3 order by 1,2 desc; this is the another solution that i have thought

[@innominatesoloist1597]

great

[@sheebakhan9920]

%sql with cte as ( select *,count(1) over (partition by spend_date,user_id order by spend_date,user_id) as cnt from spending1 ), plat as (select spend_date,user_id,amount,(case when cnt=2 then 'both' else platform end) as platform_new from cte union all select distinct spend_date, null as user_id, 0 as amount, 'both' as platform_new from spending1) select spend_date,max(user_id),sum(amount),max(platform_new) from plat group by spend_date,platform_new ;

[@Chathur732]

with cte as (select user_id,spend_date, sum(case when platform = 'mobile' then amount else 0 end) as M, sum(case when platform = 'desktop' then amount else 0 end) as D from spending group by user_id,spend_date) select spend_date , 'desktop' as platform, sum(case when M = 0 then D else 0 end ) as total_amount, sum(case when M = 0 then 1 else 0 end ) as total_count from cte group by spend_date union all select spend_date , 'mobile' as platform, sum(case when D = 0 then M else 0 end ) as total_amount, sum(case when D = 0 then 1 else 0 end ) as total_count from cte group by spend_date union all select spend_date , 'both' as platform, sum(case when D > 0 and M > 0 then M+D else 0 end ) as total_amount, sum(case when D > 0 and M > 1 then 1 else 0 end ) as total_count from cte group by spend_date order by spend_date

[@yeshwantkumar8181]

great brother

[@ankitbansal6]

🙂🙏

[@vyabinivenkatesan7839]

--My Approach /* 1. Group by date,id,platform and get the count of id and sum of amount 2. From the previous step result group by date and id and get the id count and sum of amount 3. Now copare the count value of both step 1 and step2. If both are same then it means that that user has used either of the platforms to purchase. Else if there is a change in the count of both two columns then it means that they habve used both the platforms. 4. Again grouping results from step4 to get total users and sum of amount. But I couldn't get 'both' as '0' for '02-07-2019' as expected in the output */ Solution: with cte as ( Select spend_date,user_id,platform,count(1) count,SUM(amount) amount1 from spending group by spend_date,user_id,platform), cte1 as( Select spend_date,user_id,count(1) count_of_each_user,SUM(amount1) amount2 from cte group by spend_date,user_id), cte2 as( Select distinct cte.spend_date,cte.user_id,case when cte.count=count_of_each_user then platform else 'both' end as platform,cte1.amount2 from cte inner join cte1 on cte.spend_date=cte1.spend_date and cte.user_id=cte1.user_id) Select spend_date,platform,COUNT(platform) total_users,SUM(amount2) total_amount from cte2 group by spend_date,platform order by spend_date

[@chinmoydutta2120]

sir database script not in description

[@ankitbansal6]

It's there down . Check again.

[@arpanscreations6954]

My Solution before watching the full video. It doesn't show the both platform if there no such transaction. with grouped_data as ( select user_id, spend_date, replace(group_concat(platform), 'mobile,desktop', 'both') as platform, sum(amount) as total_amount from spending group by spend_date, user_id ) select spend_date, platform, count(user_id) as num_users, sum(total_amount) as total_amount from grouped_data group by spend_date, platform

[@arpanscreations6954]

Compelete solution after watching the video: with grouped_data as ( select user_id, spend_date, replace(group_concat(platform order by platform), 'desktop,mobile', 'both') as platform, sum(amount) as total_amount from spending group by spend_date, user_id union all select distinct null as user_id, spend_date, 'both' as platform, 0 as total_amount from spending ) select spend_date, platform, count(user_id) as num_users, sum(total_amount) as total_amount from grouped_data group by spend_date, platform

[@shahakunal1]

Hi @Ankit here is one more wat of implementation select spend_date,platform,sum(amounts) as total_amount,sum(count) as total_users from (select spend_date,platform,amounts,count(distinct user_id) from (select a.user_id,a.spend_date,case when a.platforms = 2 then 'both' else sp.platform end as platform,a.platforms,a.amounts from spending sp JOIN ( select user_id,spend_date,count(distinct platform) as platforms,sum(amount) as amounts from spending group by user_id,spend_date order by spend_date asc) as a on a.user_id = sp.user_id and sp.spend_date = a.spend_date) as b group by spend_date,platform,amounts UNION ALL select distinct spend_date ,'both' as platform,0 as amounts,0 as count from spending)as d group by spend_date,platform order by spend_date ;

[@ShubhamRajputDataTalks]

with cte as(select *,row_number() over(partition by user_id,spend_date,user_id order by platform) as rn from spending) ,ct2 as (select user_id,spend_date,amount, Case when user_id in (select distinct(user_id) from cte where rn=2) then 'both' else platform end as platform from spending union all select NuLL as user_id,spend_date as spend_date, 0 as amount, 'both' as platform from spending) select spend_date, platform , Isnull(sum(amount),0) as t_amt, Isnull(count(user_id),0) as total_user from ct2 group by spend_date,platform order by spend_date,platform desc;

[@parveen8122]

mysql soln ==================== with cte as ( select spend_date, sum(amount) as amount , case when count(1)>1 then 'both' else platform end as devices, count(distinct user_id) as total_count from spending group by spend_date,user_id order by spend_date ) select * from cte union all select distinct spend_date , 0 as amount_1, 'both' as platform_1 , 0 as total_count from cte where spend_date not in ( select spend_date from cte c where devices = 'both')

[@sudakbhatt]

On doing UNION ALL of the dummy row, it shows the following error in Postgres: ERROR: UNION types integer and text cannot be matched LINE 22: NULL AS user_id Can anyone help me with this?

[@ankitbansal6]

Cast null as varchar

[@sudakbhatt]

@@ankitbansal6 Thank you Sir! It worked on doing CAST (NULL AS int) AS user_id

[@Dhanushts-g7x]

(my try) with cte1 as (select *,count(*) no_of_users from (select spend_date,case when group_concat(platform)="mobile" then "mobile" when group_concat(platform)="desktop" then "desktop" else "both" end as platform ,sum(amount) spend from spending group by spend_date,user_id order by spend_date) a group by spend_date,platform union select distinct spend_date,"both" as platform,0 spend,0 no_of_users from spending) select spend_date,platform,sum(spend) spend,no_of_users from cte1 group by spend_date,platform

[@sirimaddala8661]

WITH CTE AS ( SELECT user_id, spend_date, CASE WHEN LENGTH(GROUP_CONCAT(platform)) > 10 THEN 'Both' ELSE GROUP_CONCAT(platform) END AS platforms, SUM(amount) AS total_amount FROM spending GROUP BY user_id, spend_date UNION ALL SELECT DISTINCT spend_date, 'Both' AS platforms FROM spending ) SELECT spend_date, platforms, COUNT(user_id) AS user_count, SUM(total_amount) AS total_amount FROM CTE GROUP BY spend_date, platforms;

[@UMEKapishshambharkar]

I thought of using group_concat() to segregate the "both" and "mobile", "destop" and later use case Expression.

[@ankitbansal6]

Try out

[@mohit231]

This is complex

[@ankitbansal6]

Right. A tricky one ...

[@abb_raj1107]

My way of approach is different as I consider for various possible cases, input ----- user_id spend_date plat_f amount 1 2019-07-01 mobile [phone] desktop 100 2 2019-07-01 mobile [phone] desktop 100 5 2019-07-01 mobile [phone] desktop 100 2 2019-07-02 mobile [phone] desktop 100 7 2019-07-02 mobile [phone] desktop 100 9 2019-07-03 desktop 100 10 2019-07-04 mobile 100 output ------ spend_date plat_f total count_user 2019-07-01 mobile [phone] desktop 200 2 2019-07-01 both 200 1 2019-07-02 mobile [phone] desktop 200 2 2019-07-02 both 0 0 2019-07-03 mobile [phone] desktop 100 1 2019-07-03 both 0 0 2019-07-04 mobile [phone] desktop 0 0 2019-07-04 both 0 0 Query ----- with cte as( select spend_date, case when count(distinct platform) > 1 then 'both' when platform = 'mobile' then 'mobile' when platform = 'desktop' then 'desktop' end plat_f, sum(amount) sum_amt from spending group by spend_date,user_id ) ,cte2 as ( select spend_date, case when (count(distinct plat_f) < 2 or count(distinct plat_f) < 3) and plat_f not in ('both') then 'both' end flag, 0 total , 0 count_user from cte group by spend_date ) , cte3 as ( select spend_date, case when count(distinct plat_f) < 2 and plat_f not in ('desktop') then 'desktop' when count(distinct plat_f) < 2 and plat_f not in ('mobile') then 'mobile' end flag, 0 total , 0 count_user from cte group by spend_date ) select * from ( select distinct c1.spend_date,c1.plat_f , sum(sum_amt) over(partition by spend_date,plat_f) total , count(*) over(partition by spend_date,plat_f) count_user from cte c1 union all select * from cte2 where flag is not null union all select * from cte3 where flag is not null )A order by spend_date asc,plat_f desc;

[@vijaypunia]

hello ankit, isn't on 2024-07-01, the total users are two who are using platform as mobile? here is the query WITH all_spend AS ( SELECT spend_date, user_id, platform, SUM(amount) AS amount FROM spending GROUP BY spend_date, user_id, platform ) SELECT spend_date, platform, SUM(amount) AS total_amount, COUNT(DISTINCT user_id) AS total_users FROM all_spend GROUP BY spend_date, platform ORDER BY spend_date, platform DESC;

[@guptaashok121]

--Find all userids having both purchase with bothuser as ( select user_id,spend_date,sum(total) as total FROM( select platform,spend_date,user_id,sum(amount) as total from spending group by platform,spend_date,user_id ) a group by user_id,spend_date having count(1) > 1 ) , --Find different dates so that we can add 0 entry where we dont have any customer with both purchase distdates AS (select distinct spend_date from spending) select s.spend_date,'both' as platform,isnull(sum(b.total),0) as total,count(distinct b.user_id) as totalusers from distdates s inner join bothuser b on s.spend_date = b.spend_date group by s.spend_date UNION select s.spend_date, platform, sum(amount) as total,count(distinct s.user_id) as totalusers from spending s left outer join bothuser b on s.user_id = b.user_id and s.spend_date = b.spend_date where b.user_id is null group by s.spend_date,platform

[@ankitbansal6]

I think first union should have left join. Thanks for putting up comments . 🙏🙏

[@krishnapavanayitha5749]

The solution is ok, but you can optimize it a lot. Happy learning.

[@jikusandilya]

select spend_date, platform, sum(tot_amount), sum(tot_users) from ( select spend_date, case when string_agg(platform, ',') like '%,%' then 'both' else string_agg(platform, ',') end as platform, SUM(amount) as tot_amount, count(distinct user_id) as tot_users from spending group by spend_date, user_id UNION select distinct spend_date, 'both' as platform, 0 as tot_amount, 0 as tot_users from spending ) a group by spend_date, platform order by spend_date, platform desc

[@sreejitchakraborty6575]

In spark: a=spark.sql(""" with distinct_vals as( select user_id,spend_date,platform,sum(amount) as amount from d group by user_id,spend_date,platform) , desktop_mob_users as( select dv1.user_id,dv1.spend_date,'both' as platform,(dv1.amount+dv2.amount) as total_amount from distinct_vals dv1 inner join distinct_vals dv2 on(dv1.user_id=dv2.user_id) and(dv1.spend_date=dv2.spend_date) and(dv1.platform

[@prakharsrivastava6571]

WITH platform_summary AS ( SELECT spend_date, user_id, STRING_AGG(DISTINCT platform, ', ') AS platforms_combined, SUM(amount) AS total_amount FROM your_table GROUP BY spend_date, user_id ) SELECT spend_date, CASE WHEN platforms_combined = 'mobile, desktop' THEN 'both' ELSE platforms_combined END AS platform, SUM(total_amount) AS total_amount, COUNT(DISTINCT user_id) AS total_users FROM platform_summary GROUP BY spend_date, platform ORDER BY spend_date, platform;

[@tanushreepareek8208]

WITH cte AS (select user_id, spend_date, case WHEN COUNT(DISTINCt platform) =2 then 'Both' WHEN max(platform) = 'desktop' then 'desktop' else 'mobile' END AS platform, sum(amount) as total_amount from spending group by user_id, spend_date ) Select spend_date,platform,SUM(total_amount) AS total_amount, COUNT(user_id) as total_users from cte GROUP BY spend_date, platform UNION ALL SELECT DISTINCT spend_date, 'Both', 0 AS total_amount, 0 AS total_users FROM spending WHERE spend_date NOT IN ( SELECT DISTINCT spend_date FROM cte WHERE platform = 'Both' ) ORDER BY spend_date;

[@Aarohi_Zara]

with temp as ( select spend_date, min(platform) platform, sum(amount) total_amount, count(distinct user_id) total_users from spending group by spend_date, user_id having count(platform) = 1 union all select spend_date, 'both' , sum(amount) , count(distinct user_id) from spending group by spend_date, user_id having count(platform) > 1 union all select spend_date, platform, 0, 0 from spending group by spend_date, platform union all select spend_date, 'both', 0, 0 from spending group by spend_date ) select spend_date, platform, sum(total_amount) total_amount, sum(total_users) total_users from temp group by spend_date, platform order by spend_date, platform desc

[@bishwarup1429]

with cte as( select *, coalesce(lead(platform) over(partition by user_id order by user_id, spend_date), LAG(platform) over(partition by user_id order by user_id, spend_date)) as platform_2 from spending), CTE1 AS( select spend_date,'both' as platform,sum(amount) AS AMT, count(distinct user_id) AS NUM from cte where platform != platform_2 group by 1), cte2 as( select user_id, count(distinct platform) as num_platform from spending group by user_id), cte3 as( select spend_date, platform, sum(amount) AS AMT, count(spending.user_id) AS NUM from spending inner join cte2 on cte2.user_id = spending.user_id where cte2.num_platform = 1 group by 1,2,spending.user_id UNION SELECT * FROM CTE1 union SELECT DISTINCT SPEND_DATE, 'both' as platform, 0 as AMT, 0 as NUM from spending) select spend_date, platform, max(amt), max(num) from cte3 group by 1,2;

[@ankitbansal6]

Thanks for posting 👏