TIL - 250515(SQL_leetcode_3개)

문제1(링크)

# 코드 1
SELECT
    a.name Customers
FROM Customers a
LEFT JOIN Orders b
ON a.id=b.customerId
WHERE b.id IS NULL

# 코드 2
SELECT 
	name AS Customers 
FROM Customers 
WHERE id NOT IN (SELECT customerId FROM Orders);

비고 : 코드 2로도 할 수 있다. 조금 더 최적화된 코드인 거 같다.

문제2(링크)

# 코드 1
SELECT
    Department
    , Employee
    , Salary
FROM (
    SELECT
        b.name Department
        , a.name Employee
        , salary Salary
        , RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) r_num
    FROM Employee a
    JOIN Department b
    ON a.departmentId=b.id
) a
WHERE r_num = 1

# 코드 2
WITH a AS (
    SELECT
        b.name Department
        , a.name Employee
        , salary Salary
        , RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) r_num
    FROM Employee a
    JOIN Department b
    ON a.departmentId=b.id
)
SELECT
    Department
    , Employee
    , Salary
FROM a
WHERE r_num = 1

비고 : 서브쿼리보다 cte가 더 효율적인 거 같다.

문제3(링크)

WITH a AS (
    SELECT
        b.name Department
        , a.name Employee
        , salary Salary
        , DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY salary DESC) r_num
    FROM Employee a
    LEFT JOIN Department b
    ON a.departmentId = b.id
)
SELECT
    Department
    , Employee
    , Salary
FROM a
WHERE r_num < 4

비고 : 예전에 정리한 쿼리 구조로 보기 편해진 거 같아 기분이 좋다.