5-4 연습 문제 — 단계 분해 + CTE,
쪼개서 직접 짜본 SQL 만 머리에 남는다
이번 강의에서 배운 것 — 복잡한 요구사항을 작은 단계로 분해하고, CTE(WITH 절)로 묶기. 영상을 봤다고 끝이 아닙니다. 자연어로 쪼개 보고 → 손으로 WITH 절을 짜 보고 → 마지막엔 AI 와 한 단계씩 협업까지 — 직접 굴려본 사이클만 머리에 남습니다.
이번 연습의 위치
완료 ✓
강의 완료 ✓
지금 여기
섹션 5 마무리 ⭐
AI 결과 의심하기 ★
2-4에서 만든 AI 과외 선생님 세션을 그대로 쓰세요. 문제 1·2는 먼저 혼자 풀고 — 막히면 정답이 아니라 힌트만 요청하세요. 다 푼 뒤에는 본인의 답을 던지며 “내 SQL 검증해줘” 라고 요청하면, 단계 분해가 빠짐없이 됐는지까지 짚어줍니다. 문제 3은 처음부터 AI 와 함께 푸는 협업 시연입니다.
문제 1. 단계 분해
다음 요구사항을 — 몇 단계로 어떻게 쪼갤지 자연어로 정리하세요. SQL 안 짜도 OK, “무엇을 → 무엇을 → 최종” 흐름만.
"회원별 — 그 회원이 한 주문 중 가장 비싼 주문의 — 주문일과 총액."정답 보기
3 단계로 쪼개기:
| 단계 | 내용 |
|---|---|
| 1 | 주문별 총액 계산 — order_id 별 SUM(price × quantity) |
| 2 | 회원별 최대 주문 총액 — member_id 별 MAX(주문총액) |
| 3 | 단계 1 의 어떤 주문이 단계 2 의 max 와 일치 → 그 주문ID + 주문일 + 총액 추출 |
최종 출력: 회원이름, 주문일, 총액
문제 2. CTE 작성
문제 1 의 단계 분해를 — 실제 SQL (WITH 절) 로 작성 하세요.
기대 결과: 16 행 (주문한 회원당 1 행). 1위는 조성민의 290,000원짜리 주문 — 김철수 같은 매출 상위권 단골도 윗줄에 보입니다.
| member_name | 주문일 | 총액 |
|---|---|---|
| 조성민 | 2025-12-18 | 290000 |
| 오지은 | 2026-03-25 | 135000 |
| 박민수 | 2025-12-01 | 129000 |
| 김철수 | 2025-10-12 | 129000 |
| ... | ... | ... |
정답 보기
WITH order_totals AS (
-- 단계 1: 주문별 총액
SELECT
o.order_id,
o.member_id,
o.ordered_at,
SUM(p.price * oi.quantity) AS order_total
FROM
orders o
JOIN order_items oi
ON o.order_id = oi.order_id
JOIN products p
ON oi.product_id = p.product_id
GROUP BY
o.order_id,
o.member_id,
o.ordered_at
),
member_max AS (
-- 단계 2: 회원별 최대 주문 총액
SELECT
member_id,
MAX(order_total) AS max_total
FROM
order_totals
GROUP BY
member_id
)
-- 단계 3: 매칭 + 회원이름 가져오기
SELECT
m.member_name,
ot.ordered_at AS 주문일,
ot.order_total AS 총액
FROM
order_totals ot
JOIN member_max mm
ON ot.member_id = mm.member_id
AND ot.order_total = mm.max_total
JOIN members m
ON ot.member_id = m.member_id
ORDER BY
ot.order_total DESC;핵심 관찰:
WITH 이름 AS (SELECT...)로 두 단계 임시 테이블에 이름.- 단계 3 에서
order_totals와member_max를 마치 테이블처럼 사용. - 각 단계는 — 우리가 섹션 3 에서 배운 기본 SQL.
문제 3 (도전). AI 와 단계별 협업 시연
다음 복잡한 요구사항을 — AI 와 한 단계씩 협업 해서 풀어보세요.
“각 카테고리에서 — 평균 가격보다 더 비싼 상품들의 — 카테고리명, 상품명, 가격, 그 카테고리 평균가격을 같이.”
작업 단계
- AI 에 첫 메시지:
shop DB. 단계별로 짜줘.
[테이블]
- products: product_id PK, product_name, price, category_id FK
- categories: category_id PK, category_name
[1단계 요청]
카테고리별 평균 가격을 구해줘. 출력: category_id, avg_price.-
AI 응답 → DBeaver 실행 → 검증 (5행 나오는지 — 카테고리가 5개니까)
-
2단계 요청:
이 결과를 활용해서 — 각 상품이 *그 카테고리의 평균보다 비싼지* 판단.
출력: category_id, product_name, price, avg_price.-
AI 응답 → 검증.
-
마지막 요청:
방금까지의 단계를 — WITH 절로 묶어서 한 SQL 로 만들어줘.
카테고리명도 같이.자가 평가
- AI 가 1단계, 2단계 SQL 을 정확히 짰는가?
- WITH 절 묶기에서 AI 가 단계 1·2 의 별칭을 잘 활용했는가?
- 최종 결과의 행 수가 “평균보다 비싼 상품 수” 와 일치하는가?
- AI 가 마지막 SQL 에서 추가 개선 (정렬 / 카테고리명 JOIN) 을 알아서 했는가?
예상되는 최종 SQL 모범 답안
WITH category_avg AS (
SELECT
category_id,
AVG(price) AS avg_price
FROM
products
GROUP BY
category_id
)
SELECT
c.category_name,
p.product_name,
p.price,
ca.avg_price
FROM
products p
JOIN category_avg ca
ON p.category_id = ca.category_id
JOIN categories c
ON p.category_id = c.category_id
WHERE
p.price > ca.avg_price
ORDER BY
c.category_name,
p.price DESC;관찰:
- 1 단계만 CTE 로 분리, 2 단계 (비교) 는 메인 SELECT 의 WHERE 에서 직접.
- 카테고리명 JOIN 은 마지막에 한 번만.
- 우리 데이터 기준 최종 결과는 11행 — 전자기기 3 · 식품 3 · 의류 2 · 도서 2 · 뷰티 1.
AI 는 단계 분해 받고 → CTE 로 효율적으로 다시 정리합니다. 단계별로 받고 → 마지막에 통합 사이클의 강점.
자연어 분해 → 손으로 CTE → AI 와 단계별 협업까지 한 바퀴 돌았다면 — 다음은 섹션 5 의 모든 기술을 한 화면에서 처음부터 끝까지 보여주는 라이브 시연입니다.