SQL，力扣题目1127，用户购买平台

一、力扣链接

LeetCode_1127

二、题目描述

支出表: Spending

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| user_id     | int     |
| spend_date  | date    |
| platform    | enum    | 
| amount      | int     |
+-------------+---------+
这张表记录了用户在一个在线购物网站的支出历史，该在线购物平台同时拥有桌面端（'desktop'）和手机端（'mobile'）的应用程序。
(user_id, spend_date, platform) 是这张表的主键(具有唯一值的列的组合)。
平台列 platform 是一种 ENUM ，类型为（'desktop', 'mobile'）。

编写解决方案找出每天仅使用手机端用户、仅使用桌面端用户和同时使用桌面端和手机端的用户人数和总支出金额。

以 任意顺序 返回结果表。

三、目标拆解

四、建表语句

Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int)
Truncate table Spending
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('1', '2019-07-01', 'desktop', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-01', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('2', '2019-07-02', 'mobile', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-01', 'desktop', '100')
insert into Spending (user_id, spend_date, platform, amount) values ('3', '2019-07-02', 'desktop', '100')

五、过程分析

1、手机端、桌面端、手机和桌面端用户情况汇总

2、列出所有平台以及所有用户每天的使用情况

六、代码实现

with t1 as(
select user_id, spend_date, sum(amount) amount,case when count(platform) = 1 and max(platform) = 'desktop' then 'desktop' when count(platform) = 1 and max(platform) = 'mobile' then 'mobile'when count(platform) = 2 then 'both' end as platform
from Spending group by user_id, spend_date
)
,t2 as(
select distinct spend_date, 'mobile' platform from Spending
union all
select distinct spend_date, 'desktop' from Spending
union all 
select distinct spend_date, 'both' from Spending
)
select t2.spend_date, t2.platform, ifnull(sum(amount), 0) total_amount, ifnull(count(user_id), 0) total_users 
from t2
left join t1 on t2.platform = t1.platform and t2.spend_date = t1.spend_date
group by t2.spend_date, t2.platform;