一大波SQL面试题+solution+转换成Python/R的写法

楼主，我又想了很久，我觉得第一题你写的output是有点问题，按照这道题的逻辑， output的结果应该是
2018-01-01 00:00:00        both        200        1
2018-01-01 00:00:00        desktop        200        2
2018-01-01 00:00:00        mobile        200        2
2018-01-02 00:00:00        both        0        0
2018-01-02 00:00:00        desktop        100        1
2018-01-02 00:00:00        mobile        100        1

感谢楼主分享~太有心啦！

谢楼主分享～

手动点赞感谢好总结

谢谢楼主的分享！

谢谢分享 很有用 手动点赞

谢谢楼主分享！最近在看sql题但确实还没开始练sql转python/R

想问一下楼主，Q3 SQL解法里面，为什么要用max()呢？

手动点赞!!

Q1:
With channel_cnt as
(select date, member_id, count(distinct channel) channel_cnt from purch group by 1,2  having count(distinct channel) > 1)
Select *
From
(
Select a.date, a.channel, sum(spend) total_spend, count(distinct member_id) total_members
From purch a
Left join channel_cnt b on (a.date = b.date and a.member_id != b.member_id) group by 1,2
Union all
Select a.date, ‘both’ as channel, sum(spend) total_spend, count(distinct member_id) total_members
From purch a
join channel_cnt b on (a.date = b.date and a.member_id = b.member_id) group by 1,2
) order by 1