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

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.
*/

[komalpawar]

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

[mantisbrains]

very cool question and really learnt a lot. Thanks, Ankit!

with all_spend as
(select spend_date, max(platform) as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
from spending
group by user_id, spend_date
having count(distinct platform ) = 1
union all
select spend_date, 'both' as platform, sum(amount) as total_amount, count(distinct user_id) as total_users
from spending
group by user_id, spend_date
having count(distinct platform ) = 2
union all
select distinct spend_date, 'both' as platform, 0 as total_amount, null::bigint as total_users from spending)

select spend_date, platform, sum(total_amount) as total_amount, count(distinct total_users) as total_users
from all_spend
group by spend_date, platform
order by spend_date, platform desc

[kunalkumar-hlgv]

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

[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

[sivasrimakurthi]

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 :-)

[faizan]

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

[RohitKumar-zmnw]

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

[sakethchandrakolisetty]

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.

[mayurjoshi]

select user_id, spend_date,
case when count(distinct platform)>1 then 'both' else min (platform)end as platform,
sum(amount), count(distinct platform)
from spending
group by 1, 2

[shekharagarwal]

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

[vinayakjain]

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;

[pinaakgoel]

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

[tunguyenanh]

what an awsome answer! amazing good job and thank you

[anishchhabra]

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?

[gontlakowshik]

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.

[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

[harishkanta]

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 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

[akashgupta]

Hey Ankit,

My code in pgsql,

with ak as(select user_id, spend_date,
string_agg(distinct platform, ', ') sg, sum(amount) ta
from spending
group by 1, 2)

select spend_date,
case when sg='desktop, mobile' then 'both'
else sg end as platform,
ta total_amount,
count(1) total_users
from ak
group by 1, 2, 3
order by 1

[oorjamathur]

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!

[ls]

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