vlambda博客
学习文章列表

【SQL日常刷题 LeetCode Hard】1127. 用户购买平台1127. User Purchase Platform


解题思路

临时别名表v1查询每个用户，每天在多少个平台下单，统计用户每日使用平台数platform_count

按题目要求，如果某人没有同时在两个平台下单的用户，也需要显示该字段为0，

所以临时别名表v2，v3为日期+平台全连接，便于后续连接使用


子查询判断一个用户当天是否在两个平台下单platform_count，等于2则显示为both，否则就只显示平台名称。

select Spending.user_id, Spending.spend_date, case when v1.platform_count = 2 then 'both' when v1.platform_count = 1 then Spending.platform else 'chk' end platform, Spending.amount, v1.platform_countfrom Spending left join v1 on Spending.user_id = v1.user_id    and Spending.spend_date = v1.spend_date


t2中的第一个group by 仅聚合了金额（将多条条both记录合并为一条，金额相加）得到t2_1，第二个group by计算金额和用户数的聚合得到t2_2

(select spend_date, platform, sum(amount) as total_amount, count(user_id) as total_usersfrom ( select spend_date, platform, user_id, sum(amount) as amount from ( select Spending.user_id, Spending.spend_date, case when v1.platform_count = 2 then 'both' when v1.platform_count = 1 then Spending.platform else 'chk' end platform, Spending.amount, v1.platform_count from Spending left join v1 on Spending.user_id = v1.user_id and Spending.spend_date = v1.spend_date ) as t2_1 group by spend_date, platform, user_id ) as t2_2group by spend_date, platform    ) as t2

完整代码

# # Write your MySQL query statement belowwith v1 as ( select user_id, spend_date, count(platform) as platform_count from ( select user_id, spend_date, platform, sum(amount) as amount from Spending group by user_id, spend_date, platform ) as t1 group by user_id, spend_date),v2 as( select platform from Spending group by platform union select 'both' as platform),v3 as( select spend_date from Spending group by spend_date)
select t1.spend_date, t1.platform, coalesce(t2.total_amount, 0) as total_amount, coalesce(t2.total_users, 0) as total_usersfrom ( select spend_date, platform from v2, v3 ) as t1 left join ( select spend_date, platform, sum(amount) as total_amount, count(user_id) as total_users from ( select spend_date, platform, user_id, sum(amount) as amount from ( select Spending.user_id, Spending.spend_date, case when v1.platform_count = 2 then 'both' when v1.platform_count = 1 then Spending.platform else 'chk' end platform, Spending.amount, v1.platform_count from Spending left join v1 on Spending.user_id = v1.user_id and Spending.spend_date = v1.spend_date ) as t2_1 group by spend_date, platform, user_id ) as t2_2 group by spend_date, platform ) as t2 on t1.spend_date = t2.spend_date    and t1.platform = t2.platform


推荐阅读

[5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档 [5.05] 暗喻幻想港中35周年限定版1.03+DLC+金手指+王中王存档
Flask应用——我的发布&部署之道 Flask应用——我的发布&部署之道

相关文章