Leetcode 1127. 用户购买平台

https://leetcode-cn.com/problems/user-purchase-platform/

select 
c.spend_date
,c.platform
,sum(total_amount) total_amount
,sum(total_users) total_users
from(
    select
    b.spend_date      spend_date
    ,b.platform       platform
    ,sum(b.total_amount)   total_amount
    ,count(b.user_id) total_users
    from
    (
        select
        a.spend_date
        ,a.user_id
        ,case 
            when ifnull(a.mobile,0)>0 and ifnull(a.desktop,0)>0 then 'both'
            when ifnull(a.mobile,0)>0 and ifnull(a.desktop,0)=0 then 'mobile'
            when ifnull(a.mobile,0)=0 and ifnull(a.desktop,0)>0 then 'desktop'
        end platform
        ,ifnull(a.mobile,0)+ifnull(a.desktop,0) total_amount
        from
        (
            select 
            spend_date
            ,user_id
            ,MAX(CASE WHEN platform='mobile' then amount  else 0 end ) mobile 
            ,MAX(CASE WHEN platform='desktop' then amount else 0 end ) desktop
            from Spending
            group by spend_date,user_id
        )a  group by a.spend_date,a.user_id
    )b group by b.spend_date,b.platform 
    union
    select distinct spend_date,'both',0,0 from Spending
    union
    select distinct spend_date,'mobile',0,0 from Spending
    union
    select distinct spend_date,'desktop',0,0 from Spending
)c group by c.spend_date,c.platform
   order by c.total_amount,c.total_users