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

墨鱼我最爱 发表于 2019-6-18 09:37
我也打卡一下，互相监督。我是刷完就忘了，咋办。像是没做过一样

之前有看到地里的小伙伴用一个excel记录刷题和复习刷题的时间安排，符合记忆曲线的那种。。。我目前还做不到，只是一般会逼着自己复习一下刷过的题目<3 共勉呀！

626. Exchange Seats
SELECT id, student
FROM seat
WHERE id%2 = 1 AND id = (SELECT MAX(id) FROM seat)
UNION
SELECT s1.id, s2.student
FROM seat s1 JOIN seat s2 ON s1.id + 1 = s2.id
WHERE s1.id%2 = 1 AND s1.id != (SELECT MAX(id) FROM seat)
UNION
SELECT s1.id, s2.student
FROM seat s1 JOIN seat s2 ON s1.id = s2.id + 1
WHERE s1.id%2 = 0
ORDER BY id

/*分三种情况最后做union，也可以通过case when即if-elseif实现，简洁许多*/
SELECT
CASE WHEN id%2 = 1 AND id = (SELECT COUNT(*) FROM seat) THEN id
WHEN id%2 != 0 THEN id + 1
ELSE id - 1
END as id, student
FROM seat
ORDER BY id

569. Median Employee Salary
/*中位数：小于它的个数，和大于它的个数之差，绝对值小于等于1*/
SELECT id, company, salary
FROM employee e
WHERE ABS((SELECT COUNT(*) FROM employee e1 WHERE e.company = e1.company AND e.salary > e1.salary) - (SELECT COUNT(*) FROM employee e2 WHERE e.company = e2.company AND e.salary < e2.salary)) <= 1
GROUP BY company

615. Average Salary: Departments VS Company
SELECT avg1.pay_month, avg1.department_id,
CASE WHEN avg1.department_avg < avg2.company_avg THEN ‘lower’
WHEN avg1.depart_avg > avg2.company_avg THEN ‘higher’
ELSE ‘same’
END as comparison
FROM (
(SELECT EXTRACT(‘MONTH’, s1.pay_date) as pay_month, e1.department_id, AVG(s1.amount) as department_avg FROM salary as s1 JOIN employee e1 ON s1.employee_id = e1.employee_id GROUP BY pay_month, e1.department_id) avg1
JOIN
(SELECT EXTRACT(‘MONTH’, s2.pay_date) as pay_month, AVG(s2.amount) as company_avg FROM salary s2 GROUP BY pay_month) avg2
ON avg1.pay_month = avg2.pay_month

570. Managers with at Least 5 Direct Reports
SELECT name
. 1point3acres.com FROM employee e JOIN
(SELECT managerid FROM employee e1 GROUP BY managerid HAVING COUNT(DISTINCT Id) >= 5) temp
ON e.id = temp.managerid

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
.1point3acresON 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

求加wx一起刷，13820183287.

mark一下，一起加油

574. Winning Candidate
.. /* Solution1. */
SELECT c.name
FROM candidate c JOIN
(SELECT v.candidateid
FROM vote v
GROUP BY v.candidateid
ORDER BY COUNT(*) DESC
LIMIT 1) temp
ON c.id = temp.candidateid
-baidu 1point3acres
/* Solution2. Considering the tie case: 数量和最大的数量相等即可
Returning all the names with the highest number of votes.
Two types of tie cases:
1). different ids with the same name -> group by id instead of name.
2). different ids with the same number of votes -> select all the ids with the highest number of votes.
*/
SELECT c.name
FROM candidate c JOIN
(SELECT v.candidateid
FROM vote v
GROUP BY v.candidateid
HAVING COUNT(*) = (SELECT COUNT(*) FROM vote v1 GROUP BY v1.candidateid ORDER BY COUNT(*) DESC LIMIT 1)) temp
ON c.id = temp.candidateid

178. Rank Scores
解1.
SELECT score, rank() OVER (score ORDER BY score DESC) rank
FROM scores
解2. /*要求no holes between ranks，画出表可以看出rank=比当前值大的unique value个数*/
SELECT s1.score, (SELECT COUNT(DISTINCT s2.score) as cnt FROM scores s2 WHERE < s2.score > s1.score) rank
FROM scores s1
ORDER BY score DESC