# $1127. 用户购买平台
# https://leetcode-cn.com/problems/user-purchase-platform/

# SQL架构
Create table If Not Exists Spending (user_id int, spend_date date, platform ENUM('desktop', 'mobile'), amount int);
Truncate table Spending;
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');

# Write your MySQL query statement below
select
    t2.spend_date,
    t1.platform,
    sum(if(t1.platform = t2.platform, amount, 0)) as total_amount,
    count(if(t1.platform = t2.platform, 1, null)) as total_users
from
    (
        select
            'mobile' platform
        union
        select
            'desktop' platform
        union
        select
            'both' platform
    ) t1,
    (
        select
            user_id,
            spend_date,
            any_value(if(count(platform) = 2, 'both', platform)) as platform,
            sum(amount) as amount
        from
            Spending
        group by
            user_id,
            spend_date
    ) t2
group by
    t2.spend_date,
    t1.platform;

# clean-up
drop table Spending;