LC1127. 用户购买平台

FEIIEF 2021-08-27 原文

题解：

这道题太复杂了。。。主要是前两列最开始比较难想怎么做。

# Write your MySQL query statement below

select t4.spend_date, t4.platform, if(t6.spend_date is null,0,t6.amount) as total_amount,
        if(t6.spend_date is null, 0, users) as total_users 
from
(
    select spend_date, platform, t3.prk from 
    (select distinct spend_date from Spending) t1
    join 
    (
        select * from(
        select \'desktop\' as platform, \'1\' as prk 
        union all 
        select \'mobile\' as platform, \'2\' as prk 
        union all
        select \'both\' as platform, \'3\' as prk 
        )t2
    )t3
)t4     
left join 
(
    select spend_date, platform, sum(amount) as amount, count(user_id) as users from
    (
    select spend_date, user_id,
        (
            case when count(platform)>1 then \'both\' 
            else platform 
            end 
        )as platform,
        sum(amount) as amount 
    from Spending group by spend_date,user_id  
    )t5     # spend_date, user_id
    group by spend_date, platform 
)t6    # spend_date, platform
on t4.spend_date=t6.spend_date and t4.platform=t6.platform
order by t4.spend_date, t4.prk