SQL 튜터 세션 정리

SQL
LeetCode
윈도우 함수 심화 — LEAD/LAG + COALESCE 폴백, 다중 CTE + UNION ALL, 프레임 절(ROWS BETWEEN)을 이용한 이동 평균, UNION ALL로 양방향 관계 평탄화, 튜플 비교 (col1, col2) IN
Published

2026.04.22

SQL 튜터 세션 정리

날짜: 2026-04-22

오늘 푼 문제

# 문제 난이도 핵심 개념
1 LeetCode 626. Exchange Seats Medium 윈도우 함수 LEAD/LAG, COALESCE 3단 폴백, 짝수/홀수 그룹핑 (DIV)
2 LeetCode 1341. Movie Rating Medium 다중 CTE (WITH CTE1, CTE2 AS), UNION ALL + 괄호 + LIMIT, EXTRACT 날짜 필터
3 LeetCode 1321. Restaurant Growth Medium 프레임 절 ROWS BETWEEN, 이동 합계로 이동 평균 구현, ROW_NUMBER() 필터링, 3층 쿼리 구조
4 LeetCode 602. Friend Requests II: Who Has the Most Friends Medium UNION ALL로 양방향 관계 평탄화, 서브쿼리 집계 + ORDER BY … LIMIT 1
5 LeetCode 585. Investments in 2016 Medium 다중 컬럼 IN (튜플 비교), GROUP BY + HAVING COUNT 조건 필터링, ONLY_FULL_GROUP_BY 주의점

문제 1: LeetCode 626. Exchange Seats

문제 테이블

Seat(id INT PK, student VARCHAR)
-- id: 1부터 연속 증가

목표: 연속된 두 학생의 좌석 id를 바꿔라. 학생 수가 홀수이면 마지막 학생은 그대로.

풀이 코드

WITH t1 AS (
    SELECT (id + 1) DIV 2 AS team, id, student 
    FROM Seat
)
SELECT id, 
    COALESCE(
        LEAD(student, 1) OVER(PARTITION BY team ORDER BY id),
        LAG(student, 1) OVER(PARTITION BY team ORDER BY id),
        student
    ) AS student
FROM t1

핵심 개념 정리

  • (id + 1) DIV 2: (1,2)→team 1, (3,4)→team 2 처럼 2개씩 짝을 묶는 패턴. DIV는 정수 나눗셈(몫), /는 실수 반환
  • LEAD(student, 1) OVER(PARTITION BY team ORDER BY id): 같은 team 내 다음 행의 student
  • COALESCE 3단 폴백: LEAD(다음 행) → LAG(이전 행) → 본인. 팀 마지막 행에서 LEAD가 NULL이 되므로 LAG로 폴백
  • %는 나머지, DIV는 몫 — N개씩 그룹핑 패턴에는 항상 DIV

틀렸던 포인트

  • LEAD 단독 사용 시 파티션 마지막 행에서 NULL 발생 → LAG로 폴백 구조가 필요
  • 윈도우 함수는 직관적이지만 정렬·파티셔닝 비용이 있어 간단한 인덱스 연산보다 느릴 수 있음

다음에 공부할 것

  • ROW_NUMBER()를 활용해 id 비연속 상황에도 견고한 Exchange Seats 풀이 작성해보기
  • BIT_XOR, IF(id % 2, ...) 등 더 간결한 대안 풀이 탐색

문제 2: LeetCode 1341. Movie Rating

문제 테이블

Movies(movie_id INT PK, title VARCHAR)
Users(user_id INT PK, name VARCHAR)
MovieRating(movie_id INT, user_id INT, rating INT, created_at DATE)
-- PK: (movie_id, user_id)

목표: - 가장 많은 영화를 평가한 유저 이름 (동률 시 사전순) - 2020년 2월 평균 평점이 가장 높은 영화 제목 (동률 시 사전순) - 두 결과를 results 컬럼 하나로 반환

풀이 코드

WITH Count AS (
    SELECT user_id, COUNT(movie_id) AS cnt
    FROM MovieRating
    GROUP BY user_id
),
CountMovie AS (
    SELECT movie_id, AVG(rating) AS avg_rating
    FROM MovieRating
    WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002 
    GROUP BY movie_id
)
(SELECT name AS results
 FROM Users u
 INNER JOIN Count c ON u.user_id = c.user_id 
 ORDER BY cnt DESC, name ASC
 LIMIT 1)

UNION ALL

(SELECT title AS results
 FROM Movies m
 INNER JOIN CountMovie cm ON m.movie_id = cm.movie_id 
 ORDER BY avg_rating DESC, title ASC
 LIMIT 1)

핵심 개념 정리

  • WITH 절은 문장 맨 앞 한 번만 — UNION ALL 뒤에 또 붙일 수 없음 → 하나의 WITH에 CTE 여러 개를 콤마로 구분
  • UNION ALL로 서로 다른 의미의 결과 결합 (유저명 + 영화명)
  • UNION ALL + LIMIT 사용 시 각 SELECT를 괄호로 감싸야 함 — 괄호 없으면 LIMIT가 전체 결과에 적용될 위험
  • EXTRACT(YEAR_MONTH FROM created_at) = 202002: 날짜에서 연월 추출해 필터링
  • CTE 내부 ORDER BY는 무의미 — 최종 순서는 바깥 쿼리의 ORDER BY가 결정

틀렸던 포인트

  • WITH 블록을 UNION ALL 뒤에 한 번 더 쓰면 문법 오류
  • AVG, COUNT 같은 집계 함수 이름을 별칭으로 그대로 쓰면 오타 유발
  • 괄호 없이 UNION ALL + LIMIT 조합 시 의도와 다르게 동작

다음에 공부할 것

  • CTE vs 직접 JOIN: 언제 CTE로 분리하는 게 이득이고 언제 오버엔지니어링인지 감각 기르기
  • EXTRACT(YEAR_MONTH FROM ...) vs DATE_FORMAT(..., '%Y%m') vs YEAR()+MONTH(): 날짜 필터링 방법 비교
  • RANK() OVER (ORDER BY cnt DESC, name ASC)로 1위만 뽑는 윈도우 함수 방식과의 차이

문제 3: LeetCode 1321. Restaurant Growth

문제 테이블

Customer(customer_id INT, name VARCHAR, visited_on DATE, amount INT)

목표: 최소 7일치 데이터가 쌓인 날부터, 7일 이동 합계(amount)와 이동 평균(average_amount, 소수점 둘째 자리)을 visited_on 오름차순으로 출력

풀이 코드

WITH SumAmount AS (
    SELECT visited_on, 
           SUM(amount) AS amount
    FROM Customer
    GROUP BY visited_on
)
SELECT visited_on, amount, average_amount 
FROM (
    SELECT visited_on, 
        amount,
        ROUND(SUM(amount) OVER(ORDER BY visited_on ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7, 2) AS average_amount, 
        ROW_NUMBER() OVER (ORDER BY visited_on) AS rn
    FROM SumAmount
) AS t 
WHERE rn >= 7
ORDER BY visited_on ASC;

핵심 개념 정리

  • 윈도우 함수 프레임 절: ROWS BETWEEN N PRECEDING AND CURRENT ROW — N+1개 행의 이동 합계 계산
  • 7일 윈도우 = 6 PRECEDING + CURRENT ROW (현재 행도 1개로 카운트됨!)
  • 한 날짜에 여러 손님 → 이동평균 전에 일별 집계(CTE)를 먼저 해야 정확
  • SQL 실행 순서: FROM → WHERE → GROUP BY → HAVING → SELECT → 윈도우 함수 → ORDER BY → LIMIT
    • 윈도우 함수는 WHERE보다 늦게 계산 → WHERE에서 직접 윈도우 함수 결과 필터 불가 → 서브쿼리로 감싸야 함
  • 서브쿼리(derived table)에는 별칭 필수: AS t
  • 서브쿼리 안의 ORDER BY는 무의미, 최종 정렬은 바깥에서만

틀렸던 포인트

  • ROLLING_SUM 같은 전용 함수는 없음 → 일반 집계 함수 + OVER + 프레임 절 조합
  • WHERE rn >= 7을 서브쿼리 없이 직접 쓰면 오류 — 윈도우 함수 결과를 WHERE에서 참조 불가
  • 서브쿼리에 별칭 없으면 Every derived table must have its own alias 오류

다음에 공부할 것

  • 프레임 절 종류: ROWS vs RANGE의 차이, UNBOUNDED PRECEDING, N FOLLOWING 응용
  • RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW — 결측일이 있는 실무 데이터에서 더 견고한 방식

  • SUM(amount)/7 vs AVG(amount) OVER(...) — 어느 쪽이 더 적절한지 (7이 하드코딩되면 안 될 때)

문제 4: LeetCode 602. Friend Requests II: Who Has the Most Friends

문제 테이블

RequestAccepted(requester_id INT, accepter_id INT, accept_date DATE)
-- PK: (requester_id, accepter_id)

목표: 친구가 가장 많은 사람의 id와 친구 수(num)를 반환. 테스트 케이스는 단 한 명만 최다 친구를 가진다고 가정.

풀이 코드

최종 풀이 — UNION ALL로 양방향 관계 평탄화

SELECT id, COUNT(*) AS num
FROM (
    SELECT requester_id AS id FROM RequestAccepted
    UNION ALL
    SELECT accepter_id AS id FROM RequestAccepted
) AS t
GROUP BY id
ORDER BY num DESC
LIMIT 1;
  • (requester_id, accepter_id)는 방향성 있는 관계지만, 친구 관계는 대칭이므로 두 컬럼을 한 컬럼으로 쌓아 그냥 카운트하면 끝
  • 두 컬럼이 같은 종류의 값(유저 ID)을 담고 있을 때는 JOIN보다 UNION ALL이 직관적·간결
  • UNION 대신 UNION ALL: 중복 제거되면 같은 사람의 친구 관계가 합쳐져 카운트가 줄어듦

핵심 개념 정리

  • 관계 평탄화(relationship flattening): 방향성 있는 (A, B) 엣지를 UNION ALL로 세로로 쌓으면 각 엣지가 양쪽에 1번씩 기여 → 이후 GROUP BY id, COUNT(*)가 자동으로 “인접한 엣지 수 = 친구 수”를 산출
  • UNION vs UNION ALL:
    • UNION → 내부적으로 중복 제거(정렬/해시) 비용 + 이 문제에선 카운트가 망가짐
    • UNION ALL → 단순 스택, 빠르고 이 문제에 필수
  • 최댓값 찾기 정석 패턴: GROUP BY ... ORDER BY ... DESC LIMIT 1 — 단, 동점 가능성이 있으면 RANK()/DENSE_RANK() 윈도우 함수 병용 고려

틀렸던 포인트

처음엔 LEFT JOIN + 이중 집계로 접근했다가 막혔음:

-- 첫 시도 (막혔던 접근)
WITH t1 AS (
    SELECT requester_id AS id, COUNT(*) AS cnt_requester_id, cnt_accepter_id
    FROM RequestAccepted ra
    LEFT JOIN (SELECT accepter_id, COUNT(*) AS cnt_accepter_id
                FROM RequestAccepted GROUP BY accepter_id) t
        ON ra.requester_id = t.accepter_id
    GROUP BY requester_id
)
SELECT id, cnt_requester_id + cnt_accepter_id AS num
FROM t1 ORDER BY num DESC LIMIT 1
  • GROUP BY requester_id로 묶는 순간, requester로 한 번도 등장하지 않은 유저(예시에서 id=4)가 결과에서 완전히 누락
  • 조인 후 외부 COUNT(*)는 조인으로 행이 팽창된 상태를 세므로 실제 친구 수와 어긋남
  • 이미 집계된 서브쿼리 결과에 또 외부 집계를 걸면서 이중 집계 혼란

교훈: 두 컬럼에 흩어진 같은 종류의 데이터는 UNION ALL로 한 컬럼에 모으면 이후 집계가 자동으로 정리됨. JOIN은 서로 다른 속성을 연결할 때 쓰고, 같은 속성의 재배치는 UNION 계열이 정답인 경우가 많음.

다음에 공부할 것

문제 5: LeetCode 585. Investments in 2016

문제 테이블

Insurance(pid INT PK, tiv_2015 FLOAT, tiv_2016 FLOAT, lat FLOAT, lon FLOAT)
-- lat, lon은 NOT NULL 보장

목표: 아래 두 조건을 모두 만족하는 보험 가입자들의 tiv_2016 합계를 소수점 둘째 자리까지 반올림해서 반환 1. tiv_2015 값이 다른 가입자와 하나 이상 겹침 2. (lat, lon) 조합이 테이블 전체에서 유일 (같은 위치에 다른 가입자가 없어야 함)

풀이 코드

첫 풀이 — Accept은 되었지만 구조적 리스크가 있었던 버전

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(tiv_2015) > 1) 
  AND pid IN (SELECT pid
                FROM Insurance
                GROUP BY lat, lon
                HAVING COUNT(*) = 1)

개선 풀이 — 튜플 비교로 조건을 그대로 번역

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
)
  • 첫 풀이에서 두 번째 서브쿼리는 GROUP BY lat, lon 후 SELECT pid를 뽑는 구조 → 일반 MySQL ONLY_FULL_GROUP_BY 설정에선 에러가 나는 형태. LeetCode 채점 환경이 느슨해서 통과됐을 뿐, 실무 DB에선 실패할 수 있음
  • 개선 풀이는 “이 행의 (lat, lon) 조합이 테이블에서 유일한가”라는 문제 조건을 있는 그대로 SQL로 번역한 형태 → 더 읽히고 안전함

핵심 개념 정리

⭐ 1. 튜플 비교 (Row Constructor)

WHERE (lat, lon) IN (SELECT lat, lon FROM ...)
  • 괄호로 묶은 (col1, col2)는 두 값을 한 쌍으로 묶어 비교하라는 의미
  • 서브쿼리도 두 컬럼을 반환해야 함
  • CONCAT(lat, ',', lon) 같은 문자열 합성 방식보다 타입을 유지한 채 쌍으로 비교하므로 안전
    • CONCAT 방식은 구분자가 없으면 (12, 34)와 (1, 234)가 둘 다 "1234"로 충돌하는 함정이 생김

⭐ 2. GROUP BY + HAVING COUNT로 “몇 번 등장했는지” 필터링

  • HAVING COUNT(*) > 1 → 중복된 값만 골라내기 (문제의 조건 1)
  • HAVING COUNT(*) = 1 → 유일한 조합만 골라내기 (문제의 조건 2)
  • 두 용도 모두 “그룹의 크기를 조건으로 거는” 정형화된 패턴

⭐ 3. WHERE A AND B는 순차 실행이 아니라 동시 필터

  • A 조건이 먼저 걸러내고 B가 그 결과에 다시 적용되는 순차 모델 ❌
  • 각 행마다 A와 B가 동시에 참인지 평가 ✅
  • 옵티마이저가 내부적으로 평가 순서를 정하는 건 성능 이슈일 뿐, 논리적으론 한 행 단위의 결합 조건

⭐ 4. ONLY_FULL_GROUP_BY의 함정

  • GROUP BY col1, col2 이후 SELECT 절에는 집계 함수이거나 GROUP BY에 포함된 컬럼만 올 수 있음
  • 포함되지 않은 컬럼(예: pid)을 그냥 SELECT하면 각 그룹에서 어떤 값을 뽑을지 불분명 → MySQL 5.7+ 기본 설정에서 에러
  • LeetCode·일부 교육 환경은 이 모드가 꺼져 있어 “운 좋게” 통과하는 쿼리가 있을 수 있음 → 실무 환경으로 갖고 가면 바로 실패

오늘의 메타 인사이트

  • “Accept = 맞는 풀이”가 아니다. 채점 통과는 최소 기준일 뿐, 환경 설정이 엄격해지면 터지는 구조적 리스크가 있는지 매번 점검하는 습관이 필요
  • 문제 조건을 SQL로 번역할 때 “뭘 비교하고 싶은지”를 다시 확인: “unique한 pid를 뽑는다” vs “unique한 (lat, lon) 조합을 뽑는다”는 결과는 같아도 전자는 문법적으로 불안정, 후자는 조건의 자연스러운 번역
  • CONCAT 기반 조합 키 아이디어를 자연스럽게 떠올린 건 좋은 직관 — 다만 SQL이 이미 튜플 비교 문법을 지원하므로 굳이 문자열로 엮을 필요가 없음

다음에 공부할 것