🍅 1、今日真题
题目介绍： 用户购买平台 user-purchase-platform
难度困难
SQL架构
支出表:
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'）。写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
查询结果格式如下例所示：
``` Spending table: +---------+------------+----------+--------+ | user_id | spend_date | platform | amount | +---------+------------+----------+--------+ | 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 | +---------+------------+----------+--------+
Result table: +------------+----------+--------------+-------------+ | spend_date | platform | total_amount | total_users | +------------+----------+--------------+-------------+ | 2019-07-01 | desktop | 100 | 1 | | 2019-07-01 | mobile | 100 | 1 | | 2019-07-01 | both | 200 | 1 | | 2019-07-02 | desktop | 100 | 1 | | 2019-07-02 | mobile | 100 | 1 | | 2019-07-02 | both | 0 | 0 | +------------+----------+--------------+-------------+ 在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买，而用户3 仅 使用了桌面端购买。 在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。 ```
sql
select
spend_date, platform,
ifnull(sum(total_am),0) total_amount,
ifnull(sum(total_u),0) total_users
from
(
select p.spend_date, p.platform, t.total_am, t.total_u
from
(
select distinct spend_date, "desktop" platform from Spending
union
select distinct spend_date, "mobile" platform from Spending
union
select distinct spend_date, "both" platform from Spending
) p
left join
(
select spend_date,
if(count(distinct platform)=1, platform, 'both') plat,
sum(amount) total_am,
count(distinct user_id) total_u
from Spending
group by spend_date, user_id
) t
on p.platform = t.plat and p.spend_date = t.spend_date
) temp
group by spend_date, platform必须保证 desktop mobile both的顺序 ，所以 先列出三个字段
