SQL 튜터 세션 정리

SQL

LeetCode

서브쿼리와 NULL, HAVING의 동작 원리, 윈도우 함수(LAG/RANK), UNION ALL + SUM(CASE WHEN) 피벗 패턴

Published

2026.04.21

SQL 튜터 세션 정리

날짜: 2026-04-21

오늘 푼 문제

#	문제	난이도	핵심 개념
1	LeetCode 570. Managers with at Least 5 Direct Reports	Medium	서브쿼리, GROUP BY + HAVING, IN vs NOT IN의 NULL 처리
2	LeetCode 1934. Confirmation Rate	Easy	LEFT JOIN, CTE, “결과”와 “논리”의 차이, AVG(IF) 패턴
3	LeetCode 180. Consecutive Numbers	Medium	윈도우 함수 LAG, WHERE에서 윈도우 함수 불가, DISTINCT
4	LeetCode 1164. Product Price at a Given Date	Medium	GROUP BY 비집계 컬럼 함정, INNER JOIN으로 특정 행 추출, LEFT JOIN + COALESCE, RANK
5	LeetCode 1907. Count Salary Categories	Easy	UNION ALL, SUM(CASE WHEN) 피벗 패턴, GROUP BY가 못 만드는 “0” 행

풀이 코드

1. LeetCode 570. Managers with at Least 5 Direct Reports

직속 부하가 5명 이상인 매니저를 찾는 문제.

SELECT name
FROM Employee
WHERE id IN (
    SELECT managerId
    FROM Employee
    WHERE managerId IS NOT NULL
    GROUP BY managerId
    HAVING COUNT(managerId) >= 5
);

2. LeetCode 1934. Confirmation Rate

유저별로 confirmed 메시지 비율을 구하는 문제. 요청이 없는 유저는 0으로 처리.

최초 풀이 — CTE + CASE WHEN

WITH t1 AS (
    SELECT s.user_id,
        c.time_stamp, 
        CASE WHEN COALESCE(c.action, 'timeout') = 'timeout' THEN 0 ELSE 1 END AS confirmed
    FROM Signups s
    LEFT JOIN Confirmations c ON s.user_id = c.user_id
)
SELECT user_id, ROUND(SUM(confirmed) / COUNT(user_id), 2) AS confirmation_rate 
FROM t1
GROUP BY user_id;

더 우아한 대안 — AVG(IF)

SELECT s.user_id,
    ROUND(AVG(IF(c.action = 'confirmed', 1, 0)), 2) AS confirmation_rate
FROM Signups s
LEFT JOIN Confirmations c ON s.user_id = c.user_id
GROUP BY s.user_id;

요청이 없는 유저는 LEFT JOIN 결과 NULL 1행이 생겨 COUNT(user_id) = 1이 됨. SUM(confirmed) = 0이라 결과는 0이 나오지만, 분모 1은 논리적으로 틀림 (실제 요청 수는 0건). “결과가 맞다 ≠ 논리가 맞다”의 대표 사례.

3. LeetCode 180. Consecutive Numbers

연속으로 3번 이상 등장하는 숫자를 찾는 문제.

WITH t1 AS (
    SELECT num, 
           LAG(num, 1) OVER (ORDER BY id) AS lag_1,
           LAG(num, 2) OVER (ORDER BY id) AS lag_2
    FROM Logs
)
SELECT DISTINCT num AS ConsecutiveNums
FROM t1
WHERE num = lag_1 AND lag_1 = lag_2;

⚠️ 처음에는 WHERE에서 바로 LAG()를 호출해 에러 발생. 윈도우 함수는 SELECT 단계에서 계산되므로, 먼저 CTE로 분리해야 WHERE에서 참조 가능. ⚠️ DISTINCT 누락 시 1, 1, 1처럼 같은 값이 여러 번 출력됨.

4. LeetCode 1164. Product Price at a Given Date

2019-08-16 기준 각 상품의 가격을 구하는 문제. 이력이 없는 상품은 기본값 10.

방법 A — INNER JOIN으로 MAX 날짜 행 추출

WITH t1 AS (
    SELECT product_id, MAX(change_date) AS max_change_date
    FROM Products
    WHERE change_date <= '2019-08-16'
    GROUP BY product_id
), t2 AS (
    SELECT p.product_id, new_price AS price
    FROM Products p
    INNER JOIN t1 
        ON p.product_id = t1.product_id 
       AND p.change_date = t1.max_change_date
)
SELECT p.product_id, COALESCE(t2.price, 10) AS price
FROM (SELECT DISTINCT product_id FROM Products) p
LEFT JOIN t2 ON p.product_id = t2.product_id;

방법 B — RANK() 윈도우 함수

WITH ranked AS (
    SELECT product_id, new_price,
           RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rnk
    FROM Products
    WHERE change_date <= '2019-08-16'
)
SELECT p.product_id, COALESCE(r.new_price, 10) AS price
FROM (SELECT DISTINCT product_id FROM Products) p
LEFT JOIN ranked r 
    ON p.product_id = r.product_id 
   AND rnk = 1;

5. LeetCode 1907. Count Salary Categories

급여 구간별 계좌 수를 세는 문제. 해당 없는 구간도 반드시 0으로 출력.

방법 A — SUM(CASE WHEN) + UNION ALL 피벗

WITH Counts AS (
    SELECT SUM(CASE WHEN income < 20000 THEN 1 ELSE 0 END) AS low,
           SUM(CASE WHEN income BETWEEN 20000 AND 50000 THEN 1 ELSE 0 END) AS average,
           SUM(CASE WHEN income > 50000 THEN 1 ELSE 0 END) AS high
    FROM Accounts
)
SELECT 'Low Salary' AS category, low AS accounts_count FROM Counts
UNION ALL
SELECT 'Average Salary', average FROM Counts
UNION ALL
SELECT 'High Salary', high FROM Counts;

방법 B — UNION ALL로 카테고리별 개별 카운트

SELECT 'Low Salary' AS category, COUNT(*) AS accounts_count
FROM Accounts WHERE income < 20000
UNION ALL
SELECT 'Average Salary', COUNT(*)
FROM Accounts WHERE income BETWEEN 20000 AND 50000
UNION ALL
SELECT 'High Salary', COUNT(*)
FROM Accounts WHERE income > 50000;

핵심 개념 정리

⭐ HAVING 절은 “행 단위 비교”가 아니다

오늘 세션에서 가장 크게 얻은 깨달음.

-- ❌ 오해: "각 행의 change_date가 그룹의 MAX와 같은지" 비교할 거라 생각
SELECT *
FROM Products
GROUP BY product_id
HAVING change_date = MAX(change_date);

단계	대상	주체
`WHERE`	각 행	행 단위 필터
`HAVING`	각 그룹	그룹 단위 필터

HAVING 절은 그룹당 한 번만 평가된다. HAVING change_date = MAX(change_date)에서 좌변 change_date는 그룹 내 집계되지 않은 컬럼이라 임의의 값이 되어버린다. 즉 MySQL이 그룹 중 한 행의 값을 마음대로 골라서 비교해버리는 상황.

“행마다 MAX와 비교”가 필요할 때는 서브쿼리나 윈도우 함수로 풀어야 한다.

-- ✅ 서브쿼리: 각 행을 돌면서 MAX와 비교
WHERE change_date = (SELECT MAX(change_date) 
                     FROM Products p2 
                     WHERE p2.product_id = p1.product_id
                       AND change_date <= '2019-08-16')

-- ✅ 윈도우 함수: 그룹 내 순위로 특정 행 지정
RANK() OVER (PARTITION BY product_id ORDER BY change_date DESC) = 1

⭐ SUM(CASE WHEN …) + UNION ALL 피벗 패턴

GROUP BY는 데이터에 존재하는 값만 그룹을 만든다. 카테고리 중 하나라도 0건이면 해당 행이 결과에서 사라진다.

해결 패턴:

-- 1단계: 한 행에 여러 카운트를 뽑는다 (가로 방향)
WITH Counts AS (
    SELECT SUM(CASE WHEN 조건1 THEN 1 ELSE 0 END) AS cnt1,
           SUM(CASE WHEN 조건2 THEN 1 ELSE 0 END) AS cnt2,
           SUM(CASE WHEN 조건3 THEN 1 ELSE 0 END) AS cnt3
    FROM 테이블
)
-- 2단계: UNION ALL로 세로 방향으로 변환 (피벗)
SELECT '카테고리1', cnt1 FROM Counts
UNION ALL
SELECT '카테고리2', cnt2 FROM Counts
UNION ALL
SELECT '카테고리3', cnt3 FROM Counts;

테이블을 1번만 스캔 → 대용량에서 유리
대시보드·리포트 쿼리에서 자주 쓰는 패턴
R의 rbind / bind_rows와 비슷한 역할

윈도우 함수는 WHERE 절에서 직접 사용 불가

SQL 실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

윈도우 함수는 SELECT 단계에서 계산되므로 WHERE보다 뒤에 나온다. 따라서 WHERE에서 쓰려면 CTE나 서브쿼리로 먼저 계산해놓아야 한다.

-- ❌ 에러
SELECT num FROM Logs WHERE num = LAG(num) OVER (ORDER BY id);

-- ✅ CTE로 분리
WITH t AS (SELECT num, LAG(num) OVER (ORDER BY id) AS prev FROM Logs)
SELECT num FROM t WHERE num = prev;

RANK vs ROW_NUMBER vs DENSE_RANK

점수	RANK	DENSE_RANK	ROW_NUMBER
100	1	1	1
100	1	1	2
90	3	2	3
80	4	3	4

RANK: 동점 허용, 다음 순위 건너뜀
DENSE_RANK: 동점 허용, 다음 순위 연속
ROW_NUMBER: 동점 불허, 무조건 다른 순위

PK 조합이 중복을 막으면 세 함수 결과가 동일 → 그럴 땐 아무거나 써도 됨. 동점 처리 규칙이 결과에 영향을 줄 때만 신중히 골라야 한다.

UNION vs UNION ALL

항목	UNION	UNION ALL
중복 제거	✅	❌
성능	느림 (정렬 + 중복 제거)	빠름
기본 선택	필요할 때만	기본값으로 추천

중복이 논리적으로 생길 수 없을 때는 무조건 UNION ALL. 중복 제거가 꼭 필요할 때만 UNION.

IN vs NOT IN에서 NULL 처리

-- IN: NULL이 섞여도 다른 값 매칭은 정상
5 IN (1, 2, NULL)     → FALSE OR FALSE OR NULL → NULL → 해당 행 제외
-- NOT IN: NULL 하나가 전체를 무효화 ⚠️
5 NOT IN (1, 2, NULL) → NOT(NULL) → NULL → 모든 행 제외됨

NOT IN을 쓸 때는 반드시 NULL 필터를 함께 걸거나, NOT EXISTS / LEFT JOIN ... IS NULL 패턴으로 대체.

COUNT(*) vs COUNT(컬럼)

COUNT(*)     -- NULL 포함 모든 행 카운트
COUNT(col)   -- col이 NULL이 아닌 행만 카운트

LEFT JOIN 이후 NULL 행이 생길 때, COUNT(*)와 COUNT(컬럼) 결과가 달라질 수 있음. 비율 계산 시 분모를 뭘로 잡을지 꼭 의식할 것.

오늘의 메타 정리

⭐ 기억에 남은 인사이트

HAVING은 한 행씩 비교하지 않는다. 그룹 단위로 한 번만 평가되며, 집계되지 않은 컬럼은 임의값이 된다. 행 단위 비교가 필요하면 서브쿼리나 윈도우 함수를 써야 한다.
SUM(CASE WHEN …) → UNION ALL 피벗 패턴. GROUP BY가 못 만드는 “0” 행을 강제로 포함시키는 우아한 해결책. 테이블을 1번만 스캔하며 대시보드 집계에 최적.

반복된 실수 패턴 ⚠️

GROUP BY에 없는 컬럼을 SELECT에 포함 (1164번, 1934번 문제)
문제 명세 꼼꼼히 안 읽기 (기본값 10 → 0으로 실수, “Average” → “Avarage” 오타)
DISTINCT 누락 (180번 문제)

잘한 점

여러 번 질문으로 파고들면서 “왜 동작하는지”까지 도달함
한 문제를 두 가지 접근법으로 풀어보며 비교 관점 확보 (1164번, 1907번)
같은 실수를 두 번째 만났을 때 스스로 인식하고 넘어감

다음에 공부할 것

NOT IN + NULL 폭발 케이스 → LEFT JOIN + IS NULL / NOT EXISTS로 대체하는 패턴 정리
윈도우 함수 확장: LEAD(), FIRST_VALUE(), LAST_VALUE(), NTILE()
상관 서브쿼리(Correlated Subquery)와 윈도우 함수 성능 비교 — EXPLAIN 실행 계획 확인
GROUP BY vs 윈도우 함수 선택 기준 — 언제 뭘 쓸지 결정하는 프레임워크
“PIVOT” 패턴 심화: SUM(CASE WHEN) + UNION ALL 외에도 CROSS JOIN 활용한 카테고리 매트릭스 작성법