1127. 用户购买平台

![]6I4GR3`FR5){2C6UZP81E.png

我的解法：
关键在于需要事先构造形如result前2列的表，否则最终结果没有0对应的行
将(desktop,mobile,both)映射为(-1,1,0),这样通过sum(‘desktop’)-sum(‘mobile’)便可判定
用户是仅用mobile or desktop 还是 both

with t as(
    select spend_date,'desktop' platform, 1 num from Spending
    union
    select spend_date,'mobile', -1 from Spending
    union
    select spend_date,'both', 0 from Spending
    )
select t.spend_date,t.platform
,ifnull(sum(amounts),0) total_amount
,count(distinct user_id) total_users from
t left join
(select *,sum(platform='desktop')-sum(platform='mobile') cnt, sum(amount) amounts
from Spending group by user_id,spend_date) a
on t.spend_date = a.spend_date and t.num = a.cnt
group by t.spend_date,t.platform

附上评论区解法，思路更直接

select t2.spend_date, t2.platform, ifnull(sum(amount), 0) as total_amount,
count(distinct user_id) as total_users
from
(   #1.构造所需的表
    select distinct spend_date, "desktop" as platform
    from Spending
    union
    select distinct spend_date, "mobile" as platform
    from Spending
    union
    select distinct spend_date, "both" as platform
    from Spending    
) as t2
left join 
(   #2.查询每个用户，每个日期，每个平台类型，总金额
    select spend_date, user_id, sum(amount) as amount,
    if(count(*)=1,platform,'both') as platform
    from Spending 
    group by spend_date, user_id
) as t1
#3.左连接，并按日期和平台分组
on t2.spend_date = t1.spend_date and t2.platform = t1.platform
group by t2.spend_date, t2.platform