[SQL] LEETCODE SQL 50 문제풀이

복기

[SQL] LEETCODE SQL 50 문제풀이

nahowo 2025. 3. 6. 18:32

1193. Monthly Transactions I

https://leetcode.com/problems/monthly-transactions-i/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(id) AS trans_count,
    SUM(state = 'approved') AS approved_count,
    SUM(amount) AS trans_total_amount,
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount
FROM
    Transactions
GROUP BY
    DATE_FORMAT(trans_date, '%Y-%m'), country

나라별/달별 group by를 통해 특정 조건의 개수/비율을 계산하는 문제이다. IF를 사용하는 방법을 몰라서 서브쿼리로 해결하려다 시간을 많이 썼다…
IF(조건문, 참일 때의 값, 거짓일 때의 값)을 사용하면 서브쿼리 없이 집계함수만으로도 해결할 수 있다.

1174. Immediate Food Delivery II

https://leetcode.com/problems/immediate-food-delivery-ii/?envType=study-plan-v2&envId=top-sql-50

SELECT
    ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage
FROM
    Delivery
WHERE
    (customer_id, order_date) IN
    (
        SELECT
            customer_id,
            MIN(order_date) AS order_date
        FROM
            Delivery
        GROUP BY
            customer_id
    )

처음에 서브쿼리로 풀었는데 좋은 풀이가 있었다. IN을 사용할 때 컬럼을 여러 개 사용할 수 있다는 것을 배웠다.

550. Game Play Analysis IV

https://leetcode.com/problems/game-play-analysis-iv/submissions/1561984060/?envType=study-plan-v2&envId=top-sql-50

SELECT
    ROUND(( COUNT(player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity)), 2) AS fraction
FROM
    Activity
WHERE
    (player_id, event_date) IN (
        SELECT
            player_id,
            DATE_ADD(MIN(event_date), INTERVAL +1 DAY) AS event_date
        FROM
            Activity
        GROUP BY
            player_id
    )

위 문제와 비슷한 방식으로 다시 풀어봤다. 앞으로도 pk별 MIN/MAX 등 특정 값을 요구하는 문제는 이렇게 풀어야겠다.

619. Biggest Single Number

https://leetcode.com/problems/biggest-single-number/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    MAX(num) AS num
FROM (
    SELECT
        num
    FROM
        MyNumbers
    GROUP BY
        num
    HAVING
        COUNT(*) = 1
) AS a

만약 Single Number가 없다면 null을 출력해야 하는 문제였는데, 딱히 처리를 해주지 않아도 null이 출력되었다. min/max를 튜플이 없는 테이블에 적용하는 경우 null로 출력되는 것 같다.

1731. The Number of Employees Which Report to Each Employee

https://leetcode.com/problems/the-number-of-employees-which-report-to-each-employee/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    a.employee_id,
    a.name,
    COUNT(*) AS reports_count,
    ROUND(AVG(b.age), 0) AS average_age
FROM
    Employees AS a
JOIN
    Employees AS b
ON
    a.employee_id = b.reports_to
GROUP BY
    a.employee_id
ORDER BY
    a.employee_id

1789. Primary Department for Each Employee

https://leetcode.com/problems/primary-department-for-each-employee/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    employee_id,
    department_id
FROM (
    SELECT
        employee_id,
        department_id,
        primary_flag,
        COUNT(*) OVER (PARTITION BY employee_id) AS CNT
    FROM Employee
) AS a
WHERE
    CNT = 1
    OR
    primary_flag = 'Y'

처음에는 CNT가 하나인 튜플, flag가 Y인 튜플 따로 OR로 묶어서 연산했다.
집계 없이 COUNT만 수행한 뒤 갯수를 세면 된다.

180. Consecutive Numbers

https://leetcode.com/problems/consecutive-numbers/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    DISTINCT L1.num AS ConsecutiveNums
FROM
    Logs as L1,
    Logs as L2,
    Logs as L3
WHERE
    L1.id = L2.id - 1
    AND
    L1.num = L2.num
    AND
    L1.id = L3.id - 2
    AND
    L1.num = L3.num

FROM에 여러 테이블을 함께 넣어서 동시에 처리할 수 있다. 이는 암시적 조인으로서 크로스 조인으로 작동한다.

SELECT
    Today.id
FROM
    Weather AS Yesterday,
    Weather AS Today
WHERE
    Yesterday.recordDate = DATE_ADD(Today.recordDate, INTERVAL -1 DAY)
    AND
    Yesterday.temperature < Today.temperature

이전에 풀었던 날짜 문제도 동일하게 해결할 수 있다.

1204. Last Person to Fit in the Bus

https://leetcode.com/problems/last-person-to-fit-in-the-bus/description/?envType=study-plan-v2&envId=top-sql-50

WITH RECURSIVE tw AS (
    SELECT
        0 AS total_weight,
        0 AS turn

    UNION ALL

    SELECT
        total_weight + (SELECT weight FROM Queue WHERE turn = tw.turn + 1) AS total_weight,
        turn + 1
    FROM
        tw
    WHERE
        total_weight <= 1000
)

SELECT
    person_name
FROM
    Queue
WHERE
    turn = (
        SELECT
            MAX(turn) - 1 AS turn
        FROM
            tw
    )

순위별로 누적합을 구하기 위해 RECURSIVE를 사용했다.

SELECT
    q1.person_name
FROM
    Queue AS q1
JOIN
    Queue AS q2
ON
    q1.turn >= q2.turn
GROUP BY
    q1.turn
HAVING
    SUM(q2.weight) <= 1000
ORDER BY
    SUM(q2.weight) DESC
LIMIT 1

RECURSIVE를 사용하지 않는 풀이를 찾았다. 현재 turn 이하인 값들을 전부 현재 turn에 조인하고, 그 값들을 합하면 n부터 1까지의 누적합이 된다. 이 누적합이 1000 이하인 것들 중 최대값을 찾으면 된다.

1907. Count Salary Categories

https://leetcode.com/problems/count-salary-categories/description/?envType=study-plan-v2&envId=top-sql-50

WITH c AS (
    SELECT
        account_id,
        CASE
            WHEN income < 20000 THEN 'Low Salary'
            WHEN income BETWEEN 20000 AND 50000 THEN 'Average Salary'
            WHEN income > 50000 THEN 'High Salary'
        END AS category
    FROM
        Accounts
    UNION ALL
    SELECT null, 'Low Salary' AS category
    UNION ALL
    SELECT null, 'Average Salary' AS category
    UNION ALL
    SELECT null, 'High Salary' AS category
)

SELECT
    category,
    IFNULL(COUNT(account_id), 0) AS accounts_count
FROM
    c
GROUP BY
    category

UNION ALL을 이용해 각 항목별로 기본값을 갖도록 할 수 있다.

SELECT
    'Low Salary' AS category,
    SUM(IF(income < 20000, 1, 0)) AS accounts_count
FROM
    Accounts
UNION ALL
SELECT
    'Average Salary' AS category,
    SUM(IF(income BETWEEN 20000 AND 50000, 1, 0)) AS accounts_count
FROM
    Accounts
UNION ALL
SELECT
    'High Salary' AS category,
    SUM(IF(income > 50000, 1, 0)) AS accounts_count
FROM
    Accounts

찾아본 다른 코드에서는 각 항목별로 카운트를 했다.

1164. Product Price at a Given Date

https://leetcode.com/problems/product-price-at-a-given-date/description/?envType=study-plan-v2&envId=top-sql-50

WITH t1 AS (
    SELECT
        DISTINCT product_id,
        MAX(change_date) OVER(PARTITION BY product_id) AS last_changed
    FROM
        Products
    WHERE
        change_date <= '2019-08-16'
)

SELECT
    t1.product_id,
    p.new_price AS price
FROM
    Products AS p
JOIN t1
ON
    t1.product_id = p.product_id
    AND
    t1.last_changed = p.change_date
UNION
SELECT
    DISTINCT product_id,
    10
FROM
    Products
WHERE
    product_id NOT IN (
        SELECT
            product_id
        FROM
            t1
    )

가장 최근 변경일이 기준일 이전인 데이터를 따로 뽑고, 그것의 차집합을 UNION해서 풀었다.

SELECT
    product_id,
    IFNULL(price, 10) AS price
FROM (
    SELECT
        DISTINCT product_id
    FROM
        Products
) AS uq
LEFT JOIN (
    SELECT
        DISTINCT product_id,
        FIRST_VALUE(new_price) OVER(PARTITION BY product_id ORDER BY change_date DESC) AS price
    FROM
        Products
    WHERE
        change_date <= '2019-08-16'
) AS last_price
USING (product_id)

공식 leetcode 가이드는 WITH 대신 서브쿼리에 기준일 이전의 값들에 대해 최근 변경일을 구한 뒤, null값들에 대해 10으로 처리하는 식으로 나와있다.
FIRST_VALUE, LAST VALUE, NTH_VALUE
- NTH_VALUE(new_price, 1)와 FIRST_VALUE(new_price, 1)는 동일하다.
PARTITION BY: 그룹화할 대상 컬럼
ORDER BY: 정렬화할 대상 컬럼
- 즉 product_id 기준 그룹화하고 같은 product_id를 가지는 튜플들을 change_date 기준 내림차순 정렬하고, 거기서 최대값을 가져온다.

윈도우 함수

윈도우 함수는 튜플들에 대한 집계 함수처럼 작동한다. 하지만 집계 함수는 특정 필드에 대해 하나의 튜플만 반환하는 반면에 윈도우 함수는 각 튜플별 결과를 생성한다.
윈도우 함수는 집계 윈도우 함수와 비집계 윈도우 함수로 나뉜다.
- 집계 윈도우 함수는 OVER 구문과 함께 쓰여야 윈도우 함수가 될 수 있다. MAX, MIN, SUM이 이에 해당한다. 만약 이 윈도우 함수들을 OVER 구문 없이 사용한다면 집계 함수처럼 작동하게 된다.
- LEAD, LAG, RANK, FIRST_VALUE 등의 비집계 윈도우 함수들은 OVER 구문과 함께 쓰여야 한다.
위의 내용이 leetcode 풀이에서 GROUP BY로 묶지 않고 PARTITION BY를 사용하는 이유이다.

626. Exchange Seats

https://leetcode.com/problems/exchange-seats/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    CASE
        WHEN
            id % 2 = 1
            AND
            id != (SELECT MAX(id) FROM Seat)
            THEN id + 1
        WHEN id % 2 = 0 THEN id - 1
        ELSE id
    END AS id,
    student
FROM
    Seat
ORDER BY
    id

연속된 두 학생의 자리를 스왑하는 문제였다. id가 홀수일 때는 +1을 하고, 짝수일 때는 -1을 하면 된다. 만약 전체 학생이 홀수명이라면 마지막 학생의 자리는 바꾸지 않는다.

1341. Movie Rating

https://leetcode.com/problems/movie-rating/description/?envType=study-plan-v2&envId=top-sql-50

(SELECT
    u.name AS results
FROM (
    SELECT
        user_id,
        COUNT(*) AS cnt
    FROM
        MovieRating
    GROUP BY
        user_id
) as r
JOIN
    Users AS u
USING
    (user_id)
ORDER BY
    r.cnt DESC, u.name
LIMIT 1)

UNION ALL

(SELECT
    m.title AS results
FROM (
    SELECT
        movie_id,
        AVG(rating) AS rating
    FROM
        MovieRating
    WHERE
        MONTH(created_at) = '2'
        AND
        YEAR(created_at) = '2020'
    GROUP BY
        movie_id
) as r
JOIN
    Movies AS m
USING
    (movie_id)
ORDER BY
    r.rating DESC, m.title
LIMIT 1)

왜 두 결과를 한 테이블에서 요구하는지 모르겠다…
둘 다 각각 답을 만든 뒤 UNION하면 되는데, 사람 이름과 영화 이름이 같을 수 있기 때문에 UNION ALL을 해줘야 한다.
LIMIT을 사용하는 테이블을 UNION하려면 괄호로 묶어줘야 오류가 나지 않는다.

(
    SELECT
        name AS results
    FROM
        MovieRating
    JOIN
        Users
    USING
        (user_id)
    GROUP BY
        name
    ORDER BY
        COUNT(*) DESC, name
    LIMIT 1
)

UNION ALL

(
    SELECT
        title AS results
    FROM
        MovieRating
    JOIN
        Movies
    USING
        (movie_id)
    WHERE
        MONTH(created_at) = '2'
        AND
        YEAR(created_at) = '2020'
    GROUP BY
        title
    ORDER BY
        AVG(rating) DESC, title
    LIMIT 1
)

1321. Restaurant Growth

https://leetcode.com/problems/restaurant-growth/description/?envType=study-plan-v2&envId=top-sql-50

WITH d AS (
    SELECT
        visited_on,
        SUM(amount) AS amount,
        DENSE_RANK() OVER (ORDER BY visited_on) AS day
    FROM
        Customer
    GROUP BY
        visited_on
)

SELECT
    c1.visited_on,
    SUM(c2.amount) AS amount,
    ROUND((SUM(c2.amount) / 7 ), 2) AS average_amount
FROM
    d AS c1
JOIN
    d AS c2
ON
    c1.day >= c2.day
    AND
    c1.day <= c2.day + 6
    AND
    c1.day >= 7
GROUP BY
    c1.day
ORDER BY
    c1.visited_on

DENSE_RANK를 통해 몇 번째 날인지 확인하고, 버스 문제처럼 셀프 조인을 통해 구간합을 찾았다.

1667. Fix Names in a Table

https://leetcode.com/problems/fix-names-in-a-table/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    user_id,
    CONCAT(UPPER(SUBSTRING(name, 1, 1)), LOWER(SUBSTRING(name, 2))) AS name
FROM
    Users
ORDER BY
    user_id

SUBSTRING(자를 문자열, 시작 위치, 자를 길이)에서 시작 위치는 1부터 시작한다…

1527. Patients With a Condition

https://leetcode.com/problems/patients-with-a-condition/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    *
FROM
    Patients
WHERE
    conditions LIKE 'DIAB1%'
    OR
    conditions LIKE '% DIAB1%'

REGEXP 쓰는 풀이도 있던데 그냥 LIKE 쓰는 게 편할 것 같다.

196. Delete Duplicate Emails

https://leetcode.com/problems/delete-duplicate-emails/description/?envType=study-plan-v2&envId=top-sql-50

DELETE
    p1
FROM
    Person AS p1, Person AS p2
WHERE
    p1.email = p2.email
    AND
    p1.id > p2.id

DELETE를 사용하는 문제이다.

176. Second Highest Salary

https://leetcode.com/problems/second-highest-salary/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    IF(COUNT(*) = 0, null, salary) AS SecondHighestSalary
FROM (
    SELECT
        salary,
        DENSE_RANK() OVER (ORDER BY salary DESC) AS ranking
    FROM
        Employee
) AS a
WHERE
    ranking = 2

DENSE_RANK로 순위를 매긴 다음 IF문으로 없을 경우 null 처리를 해주면 된다.

SELECT
    (
        SELECT
            DISTINCT Salary
        FROM
            Employee
        ORDER BY
            Salary DESC
        LIMIT 1
        OFFSET 1
    ) AS SecondHighestSalary

SELECT문에 FROM이 없어도 되는걸 처음 알았다. 이렇게 작성하면 null 처리를 따로 해주지 않아도 된다.
LIMIT a, b : a부터 b까지 가져오기
OFFSET a : a부터 가져오기

1484. Group Sold Products By The Date

https://leetcode.com/problems/group-sold-products-by-the-date/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    DATE_FORMAT(sell_date, '%Y-%m-%d') AS sell_date,
    COUNT(DISTINCT product) AS num_sold,
    GROUP_CONCAT(DISTINCT product ORDER BY product) AS products
FROM
    Activities
GROUP BY
    sell_date

GROUP_CONCAT을 통해 그루핑한 튜플을 연결할 수 있다.
(SEPARAOTR ‘문자’)를 추가하면 컴마 말고 다른 문자열도 사용이 가능하다.
코드에 나와있듯이 ORDER BY, DISTINCT 설정이 가능하다.

585. Investments in 2016

https://leetcode.com/problems/investments-in-2016/description/?envType=study-plan-v2&envId=top-sql-50

SELECT
    ROUND(SUM(tiv_2016), 2) AS tiv_2016
FROM
    Insurance
WHERE
    tiv_2015 IN (
    SELECT
        tiv_2015
    FROM
        Insurance
    GROUP BY
        tiv_2015
    HAVING
        COUNT(*) > 1
    )
    AND
    (lat, lon) IN (
        SELECT
            lat,
            lon
        FROM
            Insurance
        GROUP BY
            lat, lon
        HAVING
            COUNT(*) = 1
    )

조건을 각각 서브쿼리로 만들어서 적용했다.

185. Department Top Three Salaries

https://leetcode.com/problems/department-top-three-salaries/description/

SELECT
    d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM (
    SELECT
        *,
        DENSE_RANK() OVER(PARTITION BY departmentId ORDER BY salary DESC) AS tier
    FROM
        Employee
) AS e
JOIN
    Department AS d
ON
    d.id = e.departmentId
WHERE
    e.tier <= 3

DENSE_RANK로 미리 순위를 메긴 뒤에 그 순위가 3 이하인 것들만 선택했다.

저작자표시