SQL刷题记录 求战友互相督促交流

转专业备战秋招DA/DS方向，之前零零散散上过一些网课，刷过hackerrank和leetcode没被锁的题目，但实际面试时发现思路会不清楚，被follow up一题多解时会很慌，还是功夫不够深。之前发帖求组队刷题，发现非CS专业的小伙伴们还是有分享刷题心得的需求的，不如开帖公开记录交流，顺便督促拖延症的自己好好刷题 :)
打算每楼一个题目，附上自己的解法/搜到的解法/一些fine points，便于交流。地里SQL题源比较分散，面经也不太有标准答案，有兴趣的小伙伴不妨也把自己的解法/找到的题目发上来，互相帮助，提升刷题效率，最后都能拿到理想的offer！






补充内容 (2019-6-13 23:14):
暑假基本settle好了，离秋招也越来越近了嘤嘤嘤。。。大家不要光收藏，一起来认真刷题呀！！

补充内容 (2019-6-19 21:18):
求走过路过的战友多加大米啊！地里的宝贝面经都看不到啊！QAQ

507. Friend Requests I: Overall Acceptance Rate
/*acceptance rate = # acceptance divide # requests in a given time period. leetcode上跑不出来，select语句报syntax error....求高人指点！*/
SELECT ROUND(IFNULL((COUNT DISTINCT a.requester_id, a.accepter_id)/(COUNT DISTINCT r.sender_id, r.send_to_id), 0), 2) as accept_rate
FROM friend_request r LEFT JOIN request_accepted a
ON r.sender_id = a.requester_id AND r.send_to_id = a.accepter_id

/*网上搜到的解法：这里是求overall rate，且强调accepted requests are not necessarily from request table，所以不需join，两表分别select做count即可*/
SELECT ROUND(IFNULL(SELECT COUNT(DISTINCT requester_id, accepter_id) FROM request_accepted)/SELECT COUNT(DISTINCT sender_id, send_to_id) FROM friend_request), 0), 2) as accept_rate

/*follow-up: accept rate for every month*/
网上的解法，还是分别从两表select count，但要取二者共有的month，用month函数做截取，然后用其中一个month做group by
SELECT ROUND(IFNULL(a.accepted_cnt/r.request_cnt, 0), 2) as accept_rate, r.month
FROM (SELECT COUNT(DISTINCT requester_id, accepter_id) as accepted_cnt, MONTH(request_date) as month FROM request_accepted) a
JOIN (SELECT COUNT(DISTINCT sender_id, send_to_id) as request_cnt, MONTH(accept_date) as month FROM friend_request) r
ON a.month = r.month
GROUP BY a.month

既然leetcode上没有答案，就放上我自己的解法继续求一波指点……
SELECT DATE_FORMAT(accept_date, ‘%Y-%m’) as month, IFNULL(ROUND(COUNT (DISTINCT a.requester_id, a.accepter_id) / COUNT(DISTINCT r.sender_id, r.send_to_id) as accep_rate), 2), 0)
FROM friend_request r LEFT JOIN request_accepted a
ON r.sender_id = a.requester_id AND r.send_to_id = a.accepter_id
GROUP BY DATE_TRUNC(‘MONTH’, a.accept_date)

/*follow-up: cumulative accept rate for every day*/
/*cumulative: 对每一天，计算截至这一天之前的total accept number/request number。疑问：用union来取两表时间的并集，不一定能够覆盖到每一天，那么如何将表中没有的date也加入date column？aka.如何新建一列在指定范围内auto-increment的时间列？*/
SELECT dates.date, ROUND(IFNULL(COUNT(DISTINCT a.requester_id, accepter_id)/COUNT(DISTINCT r.sender_id, r,send_to_id), 0), 2) as accept_rate
FROM friend_request r JOIN request_accepted a
JOIN (SELECT request_date as date FROM r
UNION SELECT accept_date as date FROM a
ORDER BY date) dates
ON r.sender_id = a.requester_id AND r.send_to_id = a.accepter_id
AND r.request_date <= dates.date AND a.accept_date <= dates.date
GROUP BY dates.date

Facebook Marketplace 这个题在地里面经多次出现，然而以我的积分能看到的十分有限……感谢此贴的面经和讨论，收获良多 https://www.1point3acres.com/bbs ... science-481715.html
（再次跪求各位，如果本帖对你们有帮助，求加米呀！）

Session table
Date | sessionid | userid | action (enter/click/send/exit)
Time table  （sessionid都是unique的）
Date | Sessionid | time_spent (s)

Q1: Average sessions/user per day within the last 30 days
find the #sessions per day per userid, filter data in the last 30 days, then avg
SELECT date, COUNT(DISTINCT sessionid)/COUNT(DISTINCT userid) as ratio
FROM session
WHERE DATEDIFF(date, CURENT_DATE()) <= 30
GROUP BY date
.1point3acresORDER BY date

Q2: Time distribution of user 先问大概会是怎么样的一个分布。Calculate the time spent distribution on marketplace by users for a day? (x-axis is ts bucket, and y axis is number of users)
/*First calculate the total time spent for each user, build a temp table to match the time spent in the time table with its user_id in the session table, using the session_id. Then group by time spent and count how many users are in each bucket, group by date. 疑问：我认为left join是必要的，因为要average over all users regardless of him having time spent or not；然而是不是这两个table的sessionid是完全重合的呀，如果完全重合的话似乎inner join也没问题？*/
SELECT temp.total_time_spent, COUNT(DISTINCT temp.userid)
FROM (SELECT s.userid, SUM(t.time_spent) as total_time_spent
FROM session s LEFT JOIN time t
ON s.session_id = t.session_id
GROUP BY s.userid) temp
GROUP BY 1
ORDER BY 1
可能的问题：session left join time，由于user同一个sessionid下可能有多个action，会导致出现大量重复sessionid。解决方案也许可以是关注一下action，比如exit action可以刨去不算。

/*另外一种理解方式来做：由于session table中sessionid是primary key, 先用sessionid来group by time spent，最后join回time表*/
. Waral dи,SELECT temp.total_time_spent, COUNT(DISTINCT user_id)
FROM time LEFT JOIN
(SELECT sessionid, SUM(time_spent) as total_time_spent
FROM session
GROUP BY sessionid) temp
ON t.sessionid = temp.sessionid
GROUP BY 1
ORDER BY 1
可能的问题：把所有user都视为一样的了，然而比如新用户和老用户在time spent上会有很大不同。
求有心的小伙伴们共同讨论此题！！

按user_id尾数随机抽样2000个用户？

SELECT *
FROM user u1
WHERE user_id%10 = CAST(FLOOR(RAND()*10) AS INT)
ORDER BY user_id
LIMIT 2000

/*第一次碰到用sql做随机抽样的题目，毫无头绪，查了一下发现似乎算是经典题型。随机抽样的一般写法是：
SELECT *
FROM user
ORDER BY RAND()
LIMIT 1

实际上在order by 中用rand()速度极慢（根据官方手册You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.）可用MAX*rand()先随机筛选，再join原表。以随机抽样5人为例：
SELECT *
FROM user u1 JOIN
(SELECT FLOOR(RAND()*(SELECT MAX(id) FROM user)) AS id) AS u2
WHERE u1.id >= u2.id
ORDER BY u1.id DESC
LIMIT 5

但是通过这样的操作得到的是5条连续的记录。解决办法：每次查询1条，一共查询5次。
SELECT *
FROM user
WHERE user.id >= (SELECT FLOOR(MAX(id)) * RAND()) FROM user)
ORDER BY user.id
LIMIT 1

根据原博(https://blog.csdn.net/Frank_Monkey_Lee/article/details/53303622)，即便这个做法需要多查几次，15万条的表查询起来只需要0.01秒不到，所以也是值得的。但不知能否generalize到需要更多样本的情况，如在本题中就要求查询2000条。

近期准备国内公司的面试，对如何计算pv、留存等用户行为数据很头疼。以下两题来自拼多多2018DA春招笔试（题目来源https://www.nowcoder.com/discuss/69801）

我们有一张用户访问表：person_visit，记录了所有用户的访问信息，包含字段：
用户id：user_id，访问时间：visit_date，访问页面：page_name，访问渠道（android,ios）：plat等

1.请统计近7天每天到访的新用户数。
SELECT new_user.date, COUNT(*) AS new_user
FROM
(SELECT user_id, MIN(visit_date) AS date
FROM person_visit
HAVING MIN(visit_date) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY user_id) AS new_user
GROUP BY new_user.date

/* 筛出最早访问日期在7日内的用户，然后按访问日期做group by */

2.请统计每个访问渠道7天前（D-7）的新用户的3日留存和7日留存率。
SELECT plat_user.first_date, plat_user.plat,
COUNT(CASE WHEN time_diff = 3 THEN user_id ELSE NULL END)/COUNT(*) AS 3_day_rate,
COUNT(CASE WHEN time_diff = 7 THEN user_id ELSE NULL END)/COUNT(*) AS 7_day_rate
FROM
(SELECT pv.user_id, new_user.first_date, DATEDIFF(DAY, pv.visit_date, new_user.first_date) AS time_diff, pv.plat
FROM person_visit pv JOIN
(SELECT user_id, MIN(visit_date) AS first_date
FROM person_visit
HAVING MIN(visit_date) >= DATE_SUB(CURDATE(), INTERVAL 7 DAY)
GROUP BY user_id) AS new_user
ON pv.user_id = new_user.user_id
GROUP BY pv.plat) AS plat_user
GROUP BY plat_user.first_date
ORDER BY plat_user.first_date

/* 思路类似，先筛出7日内的新用户，再count多少人在首次访问后的3日后有访问，即为3日留存。感觉用datediff函数先算好访问时间再取需要的n日留存做count是比较clever的做法。有个小疑问是在哪里对platform做groupby最合适 */

依然是拼多多题目，电商类看订单数。

两个表TB_0(订单号，用户名，订单金额，下单时间，商品ID)，TB_1(用户名，创建时间，余额)。提取用户余额>=10，半年前下过单买过ID=A，且半年内只买过ID=B的用户信息

SELECT *
FROM TB_1
WHERE balance >= 10
AND TB_1.user_id IN
(SELECT TB_0.user_id
FROM TB_0
WHERE ID = A
AND pay_time <= DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
AND TB_1.user_id IN
(SELECT TB_0.user_id
FROM TB_0
WHERE pay_time >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
GROUP BY TB_0.user_id
HAVING COUNT(DISTINCT ID) = 1
AND MAX(ID) = ‘B’

/* 重点是如何得到半年内只买过ID=B的用户：having count(distinct id)和max(id)的连用 */

补充内容 (2019-5-1 01:55):
这个不是拼多多，好像是阿里的

感觉题目都好难啊。。。

感谢LZ 的分享！
请问下刷完LeetCode和hackrank刷什么比较好？
多谢！

谢谢分享 同准备秋招

存下，同样开始要刷题了

mark住，一起加油