【每日SQL打卡】​​​​​​​​​​​​​​​DAY 15丨用户购买平台【难度困难】

[元亨利贞]

select t1.spend_date,t1.platform,t2.total_amount,t2.total_users
  from (select distinct spend_date,s2.platform
          from Spending,(select 'mobile' as platform
                         union
                         select 'desktop' as platform
                         union
                         select 'both' as platform)s2)t1
  left join 
       (select spend_date,platform,total_amount,count(distinct user_id) as total_users
          from (select user_id,spend_date,case when s1.total_amount > amount then 'both' else platform end as platform,s1.total_amount as total_amount
                  from (select user_id,spend_date,platform,amount,
                               sum(amount) over (partition by user_id,spend_date) as total_amount
                          from Spending)s1)s3
         group by user_id,spend_date,platform)t2
    on t1.spend_date = t2.spend_date and t1.platform = t2.platform
  order by t1.spend_date,t1.platform desc

思路有点乱，不够精简。难点在于如何增加2019-07-02的both及相关值。

[CSDN-Ada助手]

您可以前往 CSDN问答-大数据 发布问题, 以便更快地解决您的疑问