The following advanced-level SQL problems.
LeetCode 569
Median Employee Salary (Hard) [link]
If the occurrence is odd, median is at (cnt+1)/2 . If the occurrence is even, medians are at cnt/2 and cnt/2+1. Use the relationship between the index and the count number to find medians.
SELECT id, company, salary
FROM (
SELECT
id,company,salary,
row_number() OVER(PARTITION BY company ORDER BY salary) AS rnk,
COUNT(id) OVER(PARTITION BY company) AS cnt
FROM Employee
) t
WHERE rnk IN (cnt/2, cnt/2+1, (cnt+1)/2);
LeetCode 615
Average Salary: Departments vs Company (Hard) [link]
Using CASE WHEN and window function AVG.
SELECT DISTINCT *
FROM(
SELECT
DATE_FORMAT(s.pay_date, '%Y-%m') AS pay_month,
e.department_id,
CASE
WHEN AVG(s.amount) OVER(PARTITION BY DATE_FORMAT(s.pay_date, '%Y-%m'), e.department_id)> AVG(s.amount) OVER(PARTITION BY DATE_FORMAT(s.pay_date, '%Y-%m')) THEN 'higher'
WHEN AVG(s.amount) OVER(PARTITION BY DATE_FORMAT(s.pay_date, '%Y-%m'), e.department_id)< AVG(s.amount) OVER(PARTITION BY DATE_FORMAT(s.pay_date, '%Y-%m')) THEN 'lower' ELSE 'same'
END AS comparison
FROM Salary s
LEFT JOIN Employee e
ON s.employee_id = e.employee_id
)t;
LeetCode 1097
Game Play Analysis V (Hard) [link]
Using window function
SELECT event_date AS install_dt,
COUNT(player_id) AS installs,
ROUND(SUM(log_back) / COUNT(player_id), 2) AS Day1_retention
FROM(
SELECT a1.player_id,a1.event_date,
dense_rank() OVER(PARTITION BY a1.player_id ORDER BY a1.event_date) AS rnk,
IF(a2.event_date IS NOT NULL, 1, 0) AS log_back
FROM Activity a1
LEFT JOIN Activity a2
ON DATEDIFF(a2.event_date, a1.event_date) = 1 AND a1.player_id = a2.player_id
)t
WHERE rnk = 1
GROUP BY event_date;
Without window function
SELECT a1.first_date AS install_dt,
COUNT(a1.player_id) AS installs,
ROUND(COUNT(DISTINCT a2.player_id) / COUNT(a1.player_id), 2) AS Day1_retention
FROM(
SELECT player_id,
MIN(event_date) AS first_date
FROM Activity
GROUP BY player_id
) a1
LEFT JOIN Activity a2
ON DATEDIFF(a2.event_date, a1.first_date) = 1 AND a1.player_id = a2.player_id
GROUP BY a1.first_date;
