SQL Interview Questions and answers Part 48 | SQL LEET CODE Hard Problem

Do Like 👍👍, Comment & Subscribe

SQL Interview Questions and answers :
The Spending table keeps the logs of 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.

-------------------------------------------------------------------------
𝗝𝗼𝗶𝗻 𝗺𝗲 𝗼𝗻 𝗦𝗼𝗰𝗶𝗮𝗹 𝗠𝗲𝗱𝗶𝗮:🔥
-------------------------------------------------------------------------
*Instagram :

*Twitter:
-------------------------------------------------------------------------
Table and Insert SQL 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);
Insert into spending values(1,'2019-07-01','Desktop',100);
Insert into spending values(2,'2019-07-01','Mobile',100);
Insert into spending values(2,'2019-07-02','Mobile',100);
Insert into spending values(3,'2019-07-01','Desktop',100);
Insert into spending values(3,'2019-07-02','Desktop',100);

#SQL #LEETCODE #SQLInterviewQuestionsandanswers #sqlInterviewQuestions

[chintuy]

You create a unique content....I follow your channel frequently...keep up the good work and time.... please keep doing

[adityabaha]

Your SQL interview prep questions helped me boost my confidence 10 times. This helped me to crack a job @ Apple as a Data Engineer. Can't thank you enough! 🙏

[reachrishav]

Hi Sunil, I believe we should insert dummy records for mobile and desktop users too in the union, as there may be a date where some platform was not used. It should show 0 in the output for that platform.

[bishwarup]

with cte as(
select
User_Id
, Spend_date
, sum(case when Platform = 'Mobile' or Platform = 'Desktop' then 1 else 0 end) as total_dev
from spending
group by 2, 1),
cte2 as(
select
spending.User_Id
, spending.Spend_date
, spending.Platform
, spending.Amount
, cte.total_dev
from cte
inner join
spending
on cte.user_id = spending.user_id
and cte.spend_date = spending.spend_date),
cte3 as(
select
*
, case when total_dev = 2 then 'Both' else Platform end as Platform2
from cte2),
cte4 as(
select
Spend_date
, Platform2 as Platform
, sum(Amount) as Total_Amount
, count(user_id) as Total_users
from cte3
group by 1, 2
order by 1, 3),
cte5 as(
select
Spend_date
, 'Both' as Platform
, 0 as Total_Amount
, 0 as Total_User
from cte4
union
select * from cte4)
select Spend_date, Platform, max(Total_Amount) as Total_Amount, max(Total_User) as Total_User
from cte5 group by 1, 2;

[rajraj-rvii]

Thank you very much for the explaination, Just a small correction to get required output


with only_one_platform as (
select Platform, Spend_date, count(distinct(User_id)) as cnt, sum(Amount) as Amount from spending group by Spend_date, Platform
having count(distinct(Platform)) = 1),

both_platform as (
select 'Both' as Platform, a.Spend_date, count(distinct(a.User_id)) as cnt, max(b.amount) as amount from Spending a inner join (select User_id, Spend_date, sum(Amount) as amount from Spending
group by User_id, Spend_date having count(distinct(Platform)) = 2) b
on a.User_id = b.User_id
and a.Spend_date = b.Spend_date
group by a.Spend_date)

select * from only_one_platform
union all
select * from both_platform

[ashokrajann]

Can anyone explain to me the last step
i.e. How row no 6 got eliminated?
Little tricky to understand

[florincopaci]

Hello ! thank you for this video! if you will do more videos in the future with leetcode problems, do one please with question 2153.is very interesting. Thank you

[Rajesh_Mariyappan]

Sir, Your Output result is wrong. On 2019-07-01, there are two different user ids spent 100 each on desktop. So the output result row number 2 and 3 should be corrected. Row 2 in put put should have Desktop, total amount 200 and total users -2 . Row 3 should have both, total amount 300 and total users 2

[LiveWithDebasmita]

with cte as
(
select Spend_Date, user_id, count(1) as count, Sum(Amount) as Total_Amount from Spending as a group by Spend_Date, user_id
),
nextCte as
(
select Spend_date, 'Both' as platform, 0 as Total_Amount, 0 as Total_Users from
(select Spend_date, row_number() over(order by count desc) as seq from
(select Spend_date, count(Spend_Date) as count from cte group by Spend_date) as a) as a where a.seq > 1
)
select distinct a.Spend_Date, case when a.count = 2 then 'Both'
when a.count = 1 and b.Platform = 'Mobile' then 'Mobile'
when a.count = 1 and b.Platform = 'Desktop' then 'Desktop' end as Platform
, a.Total_Amount
case when a.count>1 then 1 else count end as Total_Users

from cte as a inner join Spending as b on a.Spend_Date = b.Spend_Date and a.user_id = b.user_id
union
select * from nextCte;

[subhashreekarmakar]

with cte_mobile as
(
select spend_date, user_id, platform, count(user_id) as total_users, sum(amount) as total_amount
from Spending where platform='Mobile'
group by spend_date, platform, user_id

),

cte_desktop as
(
select spend_date, user_id, platform, count(user_id) as total_users, sum(amount) as total_amount
from Spending where platform='Desktop'
group by spend_date, platform, user_id

),

uni as (
select user_id, spend_date, platform, total_users, total_amount from cte_mobile
union all
select user_id, spend_date, platform, total_users, total_amount from cte_desktop
),
uni2 as(
select user_id, spend_date,
count( user_id) total_users, sum(total_amount) as total_amt
from uni
group by user_id, spend_date
)


select distinct a.spend_date, a.total_users, b.total_amt, case when b.total_users=2 then 'both' else a.Platform end as pt
from uni a left join uni2 b
on a.user_id=b.user_id and a.spend_date=b.spend_date