[Inflearn] SQL 강의 고급 - [3]윈도우 함수 (문제풀이)

[1] LEETCODE 180. Consecutive Numbers

Write an SQL query to find all numbers that appear at least three times consecutively.

[내가 작성한 답]
SELECT L.num AS ConsecutiveNums
FROM(
select num, 
        LAG(num,1) OVER (ORDER BY id) AS num2,
        LAG(num,2) OVER (ORDER BY id) AS num3
from Logs ) L
WHERE L.num=L.num2
AND L.num2=L.num3


[정답]
SELECT DISTINCT L.NUM AS CONSECUTIVENUMS -- 중복 없게 출력!
FROM (
SELECT NUM
    , LEAD(NUM,1) OVER (ORDER BY ID) AS NEXT
    , LEAD(NUM,2) OVER (ORDER BY ID) AS AFTERNEXT
FROM LOGS
    ) L 
WHERE L.NUM = L.NEXT AND L.NUM = L.AFTERNEXT

[2] LEETCODE 184. Department Highest Salary

Write an SQL query to find employees who have the highest salary in each of the departments.

[내가 작성한 답] - 정답
SELECT D.NAME AS Department, E.NAME AS Employee, E.SALARY AS Salary
FROM (
    SELECT ID, NAME, DEPARTMENTID, SALARY, MAX(SALARY) OVER (PARTITION BY DEPARTMENTID) AS MAX_SALARY
    FROM EMPLOYEE
     ) E
    INNER JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID
WHERE E.SALARY = E.MAX_SALARY

[정답]
SELECT MS. DEPARTMENT, MS.NAME AS EMPLOYEE, MS.SALARY
FROM (
SELECT E.NAME,
		E.SALARY, 
        D.NAME AS DEPARTMENT, 
        MAX(SALARY) OVER (PARTITION BY DEPARTMENTID) MAX_SALARY
FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID
) MS
WHERE MS.SALARY = MS.MAX_SALARY

☑️ 정답에서는 inner join까지 전부 다 서브쿼리로 감싸줬음

[2] LEETCODE 185. Department Top Three Salaries

A company's executives are interested in seeing who earns the most money in each of the company's departments. A high earner in a department is an employee who has a salary in the top three unique salaries for that department.

Write an SQL query to find the employees who are high earners in each of the departments.

[내가 작성한 답] - 오답
SELECT D.NAME,
       E.NAME,
       E.SALARY,
       RANK () OVER (ORDER BY E.SALARY) AS SALARY
FROM EMPLOYEE E
    INNER JOIN DEPARTMENT D ON E.DEPARTMENTID = D.ID

오답 부분
☑️ RANK > DENSE_RANK
☑️ RANK () OVER (PARTITON BY ~ ORDER BY ~) 순서임
☑️ 순위 중 TOP3만 뽑아 > 위 쿼리를 서브 쿼리로 묶어주고 WHERE조건을 사용함 

[정답]
SELECT  T.DEPARTMENT,
        T.EMPLOYEE,
        T.SALARY
FROM(
SELECT E.NAME AS DEPARTMENT,
        E.NAME AS EMPLOYEE,
        E.SALARY,
        DENSE_RANK() OVER (PARTITION BY DEPARTMENTID ORDER BY SALARY DESC) AS DR
FROM EMPLOYEE E 
    INNER JOIN DEPARTMENT D ON E.DEPARTMENTID - D.ID
    ) T
WHERE T.DR <=3