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

ccplayer 发表于 2019-6-21 22:31
求加wx一起刷，13820183287.

加你啦，一起加油！

601. Human Traffic of Stadium
select s1.*
from stadium s1, stadium s2, stadium s3
where s1.people >= 100 and s2.people >= 100 and s3.people >= 100
and ((s1.id = s2.id - 1 and s1.id = s3.id - 2)
or (s1.id = s2.id + 1 and s1.id = s3.id - 1)
or (s1.id = s2.id + 2 and s1.id = s3.id + 1))
group by s1.id

/*This one beats 100% MS SQL online submissions 骄傲地发了个discussion嘻嘻*/
select id, visit_date, people
from
(select *,
lead(people) over(order by id) as after1,
lag(people) over(order by id) as before1,
lead(people, 2) over(order by id) as after2,
lag(people, 2) over(order by id) as before2
from stadium) temp
where people >= 100 and (
(before1 >= 100 and after1 >= 100)
or (after1 >= 100 and after2 >= 100)
or (before1 >= 100 and before2 >= 100))

crystalcc 发表于 2019-6-23 08:02
加你啦，一起加油！

我也加了，加油加油！

579. Find Cumulative Salary of an Employee
SELECT e.id, e.month, SUM(e1.salary) as salary
FROM employee e, employee e1
WHERE e.id = e1.id AND e.month >= e1.month AND e.month <= e1.month + 2
AND e.month < (SELECT max(e2.month) FROM employee e2 WHERE e.id = e2.id)
GROUP BY e.id, e.month
ORDER BY e.id, e.month DESC

608. Tree Node
/* Solution 1*/
SELECT id, CASE WHEN p_id is NULL THEN ‘Root’
                        WHEN id IN (SELECT p_id FROM tree) THEN ‘Inner’
                        ELSE ‘leaf’ END as type
FROM tree

/* Solution 2: Using nested structure IF(A, B)*/
SELECT id, IF(isnull(p_id), ‘Root’, IF(id in (SELECT p_id FROM tree), ‘Inner’, ‘Leaf’)) as type
FROM tree

哇哦还有这种题可以刷，最近实习写SQL写得头都要炸了，感觉可以做一下换换心情……

pats 发表于 2019-6-28 23:03
哇哦还有这种题可以刷，最近实习写SQL写得头都要炸了，感觉可以做一下换换心情……

我实习用的sql感觉远不如这些复杂……

610. Triangle Judgement
SELECT x, y, z, CASE WHEN x + y <= z OR x + z <= y OR y + z <= x THEN ‘No’ ELSE ‘Yes’ END as triangle
FROM triangle

180. Consecutive Numbers
/*Solution 1*/
SELECT DISTINCT l1.num
FROM logs l1, logs l2, logs l3
WHERE l1.id = l2.id - 1 AND l1.num = l2.num
AND l2.id = l3.id - 1 AND l2.num = l3.num

/*Solution 2. Note that for window functions, ORDER BY is required for FIRST_VALUE, LAST_VALUE, NTH_VALUE, LEAD and LAG*/
SELECT DISTINCT num
FROM (SELECT num, LAG(num) OVER (ORDER BY id) as before, LEAD(num) OVER (ORDER BY id) as after FROM logs) temp
WHERE temp.num = temp.before AND temp.num = temp.after

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
ORDER 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表*/
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上会有很大不同。
求有心的小伙伴们共同讨论此题！！