1127. 用户购买平台（难度：困难）

题目描述

（通过次数6,893 | 提交次数16,007，通过率43.06%）

支出表: 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 仅 使用了桌面端购买，且没有用户 同时 使用桌面端和手机端购买。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/user-purchase-platform

//测试数据
Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);

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');

解题思路

这是一道困难题。其实我觉得，并不在于题目的要求有多难实现，而是对题目本身的理解。
在题目本身的理解上，有两个坑需要避开：
第一个坑：对于同一天有两种平台购买记录的用户，只计算在both类型中，而不会再重复计算到mobile和desktop中；
强哥第一次没理解到这层含义，就重复计算了。
第二个坑：对于某天某个平台，没有任何购买记录的时候，也需要以0的结果返回。
理解了以上两点，实现起来就容易的多了。
既然题目要求，同一个人在同一天只会认定为一种类型，那我们需要考虑的重点就变成了，如何认定一个人在某一天是哪种类型。
根据题目描述，如果某一天某个人只在一个平台上有过购买记录，则他必定会被认定为这个有购买记录的平台；如果在2个平台上有过购买记录，就会被认定为both。
所以，可以通过对spend_date和user_id分组，计算platform的不重复数量，就可以得出认定结果。再根据认定结果分组汇总即可。
对于上面的第二个坑，每一天的所有组合中，即使没有购买记录也要返回，那就需要我们事先构造出一个所有组合的全集。
一般来说，我们使用笛卡尔积的方式来构造全集。
最后，我们使用这个全集与认定类型后统计出的结果左关联，再做一次分组汇总，即可得出最终结果。

参考SQL

未特别说明的情况下，参考SQL为基于MySQL8.0实现。

with
tmp1 as (
    select 'mobile' as platform
    union all
    select 'desktop' as platform
    union all
    select 'both' as platform
),
tmp2 as (
    select distinct spend_date
    from Spending
),
tmp3 as (
    select
        a.spend_date,
        b.platform
    from tmp2 a
    inner join tmp1 b
    on 1=1
),
tmp4 as (
    select
        spend_date,
        user_id,
        count(distinct platform) platform_cnt,
        min(platform) platform_min,
        sum(amount) amount
    from Spending
    group by spend_date,user_id
)
select
    a.spend_date,
    a.platform,
    coalesce(sum(b.amount),0) total_amount,
    count(b.user_id) total_users
from tmp3 a
left join tmp4 b
on a.spend_date = b.spend_date
and a.platform = case when b.platform_cnt = 1 then b.platform_min else 'both' end
group by 1,2
order by 1,2;