Leetcode Hard Problem 4 | User Purchase Platform | Complex SQL 11
HTML-код
- Опубликовано: 9 сен 2024
- In this video we are going to discuss very interesting leetcode SQL problem called User Purchase Platform . This is one of the very interesting problem that I have seen on leetcode. Do try yourself first. Below is the create and insert script:
create table spending
(
user_id int,
spend_date date,
platform varchar(10),
amount int
);
insert into spending values(1,'2019-07-01','mobile',100),(1,'2019-07-01','desktop',100),(2,'2019-07-01','mobile',100)
,(2,'2019-07-02','mobile',100),(3,'2019-07-01','desktop',100),(3,'2019-07-02','desktop',100);
/* User purchase platform.
-- The table logs the spendings history of users that make purchases from an online shopping website which has a desktop
and a mobile application.
-- Write an SQL query to find the total number of users and the total amount spent using mobile only, desktop only
and both mobile and desktop together for each date.
*/
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
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
Thanks for posting 🙏
@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.
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 :-)
Glad it was helpful!
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.
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
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
wow!!
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
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;
nice solution, but we missed second both row for value for date 2019-07-02 in output
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
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
Good effort. Thanks for posting 👏
Great and Simple solution.
the moment you inserted the dummy record i got shocked , because i have never done this type of concept before
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
what an awsome answer! amazing good job and thank you
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.
If we have a record in both, no point inserting in mobile and destop, since its self explanatory
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
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!
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
this is giving error on "spending.platform"
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?
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
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
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
;
Thanks for the question and solution.
Welcome
Thanks Ankit - Max()/Min() is game changer here
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
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..
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;
Was finally able to partially complete it all because I felt that the dummy part would not be considered.............😣😣
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
tricky question. Thanks Ankit
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
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
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!
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
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
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
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
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?
You can add dummy records for mobile and desktop with 2 more unions like we did for both category.
@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
but this solution is not working on leetcode, only sample test case passed. 1/11 test case passed
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
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
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;
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 ?
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;
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
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
Thank you for another useful video!
🙏🙏
@@ankitbansal6 1384 -total sales by year is also a very tricky question. All the best!
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
By the qiestion i thoight they eant output like
Date, mobile_sales, desktop-sales, both_sales..
%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
;
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;
This is so cool, Ankit.
Thanks 🙏
a little confusion here..
on 2019-07-01 the resultant total_users should be 2....becusase on that date two users used mobile platform
without max also it will end up with the same result, right?
Since the column is not in group by, you need to use any aggregation function.
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
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 ;
*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
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
Good effort 👍
what if july 2nd mobile was not purchased?
great brother
🙂🙏
great
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')
It took me 2 hours to understand, is this the same happening to many guys or just me?
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
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
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?
Cast null as varchar
@@ankitbansal6 Thank you Sir! It worked on doing
CAST (NULL AS int) AS user_id
sir database script not in description
It's there down . Check again.
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
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
(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
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
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
This is complex
Right. A tricky one ...
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;
--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
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);
@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
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.
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;
I thought of using group_concat() to segregate the "both" and "mobile", "destop" and later use case Expression.
Try out
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 100
1 2019-07-01 desktop 100
2 2019-07-01 mobile 100
3 2019-07-01 desktop 100
5 2019-07-01 mobile 100
6 2019-07-01 desktop 100
2 2019-07-02 mobile 100
3 2019-07-02 desktop 100
7 2019-07-02 mobile 100
8 2019-07-02 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 200 2
2019-07-01 desktop 200 2
2019-07-01 both 200 1
2019-07-02 mobile 200 2
2019-07-02 desktop 200 2
2019-07-02 both 0 0
2019-07-03 mobile 0 0
2019-07-03 desktop 100 1
2019-07-03 both 0 0
2019-07-04 mobile 100 1
2019-07-04 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;
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;
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
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
--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
I think first union should have left join. Thanks for putting up comments . 🙏🙏
The solution is ok, but you can optimize it a lot.
Happy learning.
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;
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;
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
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;
Thanks for posting 👏