[SQL] LeetCode Hard Database Problem

1. Department Top Three Salaries

//각 부서마다 연봉 top3
SELECT D.Name as Department, E.Name as Employee, E.Salary as Salary
FROM Department D
JOIN (
SELECT DepartmentId, Name, Salary, RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
FROM Employee
WHERE rank <= 3) E
ON D.Id = E.DepartmentId

2. Trips and Users

//일별 취소율 : unbanned로 부터의
SELECT Request_at as Day, SUM(Status != 'completed') / COUNT(*) as Cancellation_Rate
FROM Trips t
WHERE Request_at >= "2013-10-01" AND Request_at <= "2013-10-03"
AND Client_id NOT IN (SELECT USER_ID FROM USERS WHERE Banned = "Yes")
AND Driver_id NOT IN (SELECT USER_ID FROM USERS WHERE Banned = "Yes")
GROUP BY Request_at

3. Human Traffic of Stadium

SELECT a.*
FROM Stadium a
JOIN Stadium b JOIN Stadium c
ON (a.id = b.id-1 and a.id = c.id-2)
OR (a.id = b.id1+1 and a.id = c.id-1)
OR (a.id = b.id1+1 and a.id = c.id1+2)
where a.salary >=100 and b.salary >= 100 and c.salary > =100
order by visit_date

4. Game Play Analysis 5

//해당 날짜마다 retention율
SELECT first_Date as install_dt, COUNT(DISTINCT player_id)as installs, 
COUNT(DISTINCT a.player_id) /COUNT(DISTINCT b.player_id)as Day1_retention
FROM Acitivity A
JOIN (
SELECT player_id, MIN(event_date) as first_date
FROM Activity
GROUP BY player_id) B
ON A.event_date = B.first_date+1 and a.player_id = b.player_id
GROUP BY first_date

5. Sales by Day of the Week

//카테고리별 상품이 요일마다 얼마나 팔렸냐
SELECT Items.item_category as Category,
SUM(IF(WEEKDAY(order_date) = 0), quantity, 0) as Monday, 
SUM(IF(WEEKDAY(order_date) = 1), quantity, 0)as Tuesday, 
SUM(IF(WEEKDAY(order_date) = 2), quantity, 0)as Wednesday,
SUM(IF(WEEKDAY(order_date) = 3), quantity, 0)as Thursday, 
SUM(IF(WEEKDAY(order_date) = 4), quantity, 0)as Friday, 
SUM(IF(WEEKDAY(order_date) = 5), quantity, 0)as Saturday, 
SUM(IF(WEEKDAY(order_date) = 6), quantity, 0)as Sunday
FROM Orders
JOIN Items
ON Orders.item_id = Items.item_id
GROUP BY Items.item_category

6. Get the Second Most Recent Activity

SELECT username, activity, startDate, endDATE FROM
(SELECT username, activity, startDate, endDate,
RANK() OVER(PARTITION BY username ORDER BY endDate DESC) R,
COUNT(actitvity) OVER (PARTITION BY username) c
FROM UserActivity) lookup
WHERE r = 2 or c=1

7. Find the Quiet Students in All Exams ( IN (…UNION…) )

SELECT student_id, student_name
FROM Exam JOIN Student ON Exam.student_id = Student.student_id
GROUP BY student_id
HAVING SUM(exam_id, score) IN 
	(SELECT exam_id, max(score) FROM Exam GROUP BY exam_id
	UNION
	SELECT exam_id, min(score) FROM Exam GROUP BY exam_id)
ORDER BY student_id