Hive Solution

3.Hive Solution

SELECT
    x.user_id,
    x.spend_date,
    x.platform,
    x.total_amount,
    count(x.user_id) as total_user
FROM
(
select 
    t1.user_id,
    t1.spend_date, 
    t2.mobile_amount,
    t3.desktop_amount,
    coalesce(mobile_amount,0)+coalesce(desktop_amount,0) as total_amount,
    case when mobile_amount is not null and desktop_amount is not null then 'both'
        when mobile_amount is not null and desktop_amount is null then 'mobile_only'
        when mobile_amount is  null and desktop_amount is not null then 'desktop_only'
       END as platform
-- select *
from 
    (select distinct  user_id,spend_date from ex_1127_spending) t1
    left join
    (select  user_id,spend_date,sum(amount) as mobile_amount
        from ex_1127_spending  
            where platform ='mobile'
            group by user_id,spend_date) t2 
                    ON t1.user_id=t2.user_id and t1.spend_date=t2.spend_date
    left  join 
    (select  user_id,spend_date,sum(amount) as desktop_amount
        from ex_1127_spending  
            where platform ='desktop'
            group by user_id,spend_date) t3 
                    ON t1.user_id=t3.user_id and t1.spend_date=t3.spend_date
) x 
GROUP BY x.user_id,x.spend_date, x.platform,x.total_amount 
;