问一道SQL 题，求解答

问大家一道leetcode变形题，求解答??

given table A:
+---------+------------+----------+--------+
| user_id | date       | platform | amount |
+---------+------------+----------+--------+
| 1

您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 188 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Popular Interview Questions from Top Companies

7-01 | desktop  | 2           |
| 2019-07-01 | mobile   | 3           |
| 2019-07-01 | both     | 1           |

您好！
本帖隐藏的内容需要积分高于 100 才可浏览
您当前积分为 0。
使用VIP即刻解锁阅读权限或查看其他获取积分的方式

游客，您好！
本帖隐藏的内容需要积分高于 100 才可浏览
您当前积分为 0。
VIP即刻解锁阅读权限 或 查看其他获取积分的方式
Unlock interview details and practice with AI
Popular Interview Questions from Top Companies

我这里用了两个CTE
第一个里面先 left join 来判断 platform
第二个里面用了 union 来合并所有的可能性

根据 test case 提供的数据可以在 MySQL 里面得到如下结果：

1. WITH CTE1 as   (
   
2. SELECT DISTINCT
   
3. a.user_id,
   
4. a.date,
   
5. case
   
6. when a.platform = 'mobile' and b.platform is null then 'mobile'
   
7. when a.platform = 'desktop' and b.platform is null then 'desktop'
   
8. when a.platform is not null and b.platform is not null then 'both'
   
9. else null end as 'platform'
   
10. 
    
11. FROM platform a LEFT JOIN platform b
    
12. ON a.user_id = b.user_id
    
13. AND a.date = b.date
    
14. AND a.platform <> b.platform
    
15. ),
    
16. 
    
17. CTE2 as   (
    
18. select user_id, date, platform from CTE1
    
19. union
    
20. select user_id, date, 'mobile' as platform from CTE1 where platform = 'both'
    
21. union
    
22. select user_id, date, 'desktop' as platform from CTE1 where platform = 'both'
    
23. )
    
24. 
    
25. 
    
26. SELECT
    
27. date,
    
28. platform,
    
29. count(user_id) as total_users
    
30. 
    
31. FROM CTE2
    
32. GROUP BY date, platform
    
33. ORDER BY date, platform desc
    
34. ;

复制代码

方法比较笨，但是应该有效

1. with a as (
   
2. select date, user_id, sum(case when platform='mobile' then amount else 0 end ) as ma,sum(case when platform='desktop' then amount else 0 end) as da from A group by date, user),
   
3. ref as (select distinct date, 'mobile' as platform from A union (select distinct date,'desktop' as platform from A) union select distinct date,'both' as platform from A),
   
4. b as (select date, count(case when ma>0 and da>0 then userid else null end) as bothn, count(case when ma=0 and da>0 then userid else null end) as dn, count(case when da=0 and ma>0 then userid else null end) as mn from a group by date)
   
5. select date, platform, coalesce(total_users, 0) as total_users from (
   
6. select date, 'both' as p , bothn as total_users from b where bothn >0 union select date, 'mobile' as p, mn as total_users from b where mn>0 union select date,  'desktop' as p ,dn as total_users from b where dn>0) sub right join ref on sub.date=ref.date,sub.p=ref.platform

复制代码

combine rows，要用for, 再用一个case when

Group by date, count user id. Having date = 2019-7-1

cloverlyy 发表于 2020-11-25 01:36
Group by date, count user id. Having date = 2019-7-1

这题最终结果应该不只2019-07-01的

踩我的朋友，要是有5个platform 你会scale吗？不写for你来做一个？count(userid) = 2你能知道是哪2个platform？莫名其妙

Qomo 发表于 2020-11-25 02:19
这题最终结果应该不只2019-07-01的

嗯嗯嗯，我的答案不对。 积分不够，看不了全文。不知道答案中both是什么。。。

cloverlyy 发表于 2020-11-25 04:02
嗯嗯嗯，我的答案不对。 积分不够，看不了全文。不知道答案中both是什么。。。

both 应该是指 each user_id 既有 mobile transaction 也有 desktop transaction.

能想到的最简单的方法是用union。desktop和mobile的用group by就可以，后面both的可以self join一下date+user_id并且左边取desktop右边取mobile，两个都不是null就是1，然后sum一下，最后两个table union all起来。