题目难度
困难
题目描述
写一段 SQL 来查找每天 仅 使用手机端用户、仅 使用桌面端用户和 同时 使用桌面端和手机端的用户人数和总支出金额。
在 2019-07-01, 用户1 同时 使用桌面端和手机端购买, 用户2 仅 使用了手机端购买,而用户3 仅 使用了桌面端购买。
在 2019-07-02, 用户2 仅 使用了手机端购买, 用户3 仅 使用了桌面端购买,且没有用户 同时 使用桌面端和手机端购买。
正确答案
select t2.spend_date, t2.platform,
ifnull(sum(amount),0) total_amount,
ifnull(count(user_id),0) total_users
from
(
select distinct spend_date,t.platform
from Spending,
(select "desktop" as platform
union
select "mobile" as platform
union
select "both" as platform) t
) t2
left join
(
select spend_date, sum(amount) amount, user_id,
case when count(*) = 1 then platform else "both" end as platform
from Spending
group by spend_date, user_id) t1
on t1.spend_date = t2.spend_date and t1.platform = t2. platform
group by t2.spend_date, t2.platform
思路
先根据platform将每天的用户分成desktop,mobile,both三类;然后根据spend_date, platform统计人员数量与支出额
select spend_date,platform, sum(amount) as total_amount, count(user_id) total_users
from
(select spend_date, user_id, sum(amount) as amount,
case count(distinct platform)=2 then 'both' else platform end as platform
from Spending
group by spend_date, user_id
) as t
group by spend_date, platform
但是存在一个问题,2019-07-02没有both的用户,所以在上述代码前,先要加上一个日期与platform的框架,即正确答案中的t2表。空值用0填充。