[SQL] LeetCode Medium Database Problem (1~10)

1. Nth Hghtest Salary (FUNCTION)

CREATE FUNCTION getNth (N INT) RETURNS INT
BEGIN
	RETURN (
			SELECT Salary From Employee
			LIMIT 1, N-1
			//LIMIT 1 OFFSET N-1
);
END

CREATE FUNCTION getNth (N INT) RETURNS INT
BEGIN 
SET N = N-1
	RETURN (
			SELECT Salary From Employee
			LIMIT 1, N
			//LIMIT 1 OFFSET N
);
END

2. Rank Scores (DENSE_RANK, -보다 같거나 큰수를 세서 RANK)

//DENSE_RANK() OVER ()
SELECT score, DENSE_RANK() OVER (ORDER BY score desc)as Rank
FROM Scores

//SUBQUERY
SELECT s1.score, (SELECT COUNT(DISTINCT score) FROM Scores s2 WHERE s1.score <= s2.score) 
FROM Scores s1
ORDER BY s1.score desc

3. Consecutive Numbers (세 번 이상 연속되는 수, SELF JOIN 여러번)

SELECT AS ConsecutiveNums
FROM Logs a 
JOIN Logs b
ON a.Num = b.Num AND a.Id+1 = b.Id
JOIN Logs c
ON a.Num = c.Num AND a.Id+2 = c.Id

4. Department Highest Salary

SELECT Department, Employee.Name as Employee, Salary
FROM Department
JOIN Employee
ON Department.Id = Employee.DepartmentId
WHERE (DepartmentId, Salary) IN
(SELECT DepartmentId, MAX(Salary) FROM Employee GROUP BY DepartmentId)
//GROUP BY 사용했을 때, SELECT 컬럼에 중복이 없는가 확인.

5. Game Play Analysis 3

SELECT a.player_id, a.event_date, SUM(b.games_played)as games_played_so_far
FROM Activity a
JOIN Activity b
ON a.player_id = b.player_id AND **a.event_date >= b.event_date**
GROUP BY a.player_id, a.event_date

6. Game Play Analysis 4

SELECT ROUND(COUNT(DISTINCT a.player_id) / COUNT(DISTINCT b.player_id), 2) AS fraction
FROM Activity a
LEFT JOIN (SELECT player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as seconddate
FROM Activity
GROUP BY player_id) b
ON a.player_id = b.player_id AND a.event_date = b.seconddate

7. Managers with at Least 5 Direct Reports

SELECT Name
FROM Employee a
WHERE a.Id = (
SELECT ManagerId, COUNT(ManagerId)
FROM Employee b
GROUP BY ManagerId)

8. Exchange Seats

SELECT IF(id < (SELECT MAX(id) FROM seat),IF(id%2=0,id-1,id+1), id) as id,  student
FROM seat
ORDER BY id

9. Product Sales Analysis 3

SELECT Sales.product_id, Sales.year as first_year, Product.quantity, Product.price
FROM Sales
WHERE (Sales.product_id, year) IN 
(SELECT product_id, MIN(year) FROM Sales GROUP BY product_id)

10. All People Report to the Given Manager

SELECT employee_id
FROM Employee a
JOIN Employee b
ON a.manager_id = b.employee_id
JOIN Employee c
ON a.manager_id = c.employee_id
WHERE a.manager_id != 1 and c.manager_id = 1