실무에서 바로 꺼내 쓰는 SQL 레시피 모음 — 조인 · 집계 · 윈도우 함수 · 날짜/코호트 · 퍼널 · 피벗. 모든 예제는 합성(synthetic) 더미 데이터 기준이며, MS-SQL / MySQL / PostgreSQL에서 돌아갑니다.
MS-SQL Stored Procedure 자동화로 게임사 데이터 운영을 다지고, 이커머스·회계·마케팅 도메인까지 그대로 들고 다닌 "기본기 SQL"을 레시피 형태로 정리한 저장소입니다.
이 저장소는 같이 보면 좋은 자매 저장소들과 연결됩니다.
- game-data-recipes — 게임 지표(DAU/MAU/Retention/ARPPU) 분석 레시피 (합성 데이터)
- dart-xbrl-parser — DART 공시 XBRL 재무데이터 파싱
- python-automation-100 — 업무 자동화 파이썬 스니펫 100선
- related_kws — 멀티 검색엔진 연관 검색어 수집기
- SQL 기초 문법은 떼었지만 **"실무에서 이걸 어떻게 조합하지?"**가 막히는 분
- 코호트 리텐션 · 퍼널 · 누적합 같은 분석용 쿼리 패턴을 빠르게 베껴 쓰고 싶은 분
- MS-SQL로 짠 쿼리를 MySQL / PostgreSQL로 옮길 때 방언 차이가 궁금한 분
정체성 한 줄: AI 시대에 도메인을 가진 데이터 분석가 — 게임·이커머스·회계 도메인을 직접 코드로 분석하는 12년차. 도메인 지식 + 자동화 실행력이 무기라고 믿습니다.
| # | 주제 | 핵심 패턴 | 난이도 | 파일 |
|---|---|---|---|---|
| 01 | 조인 기본기 | INNER / LEFT / FULL OUTER / 안티조인 |
★☆☆ | recipes/01_joins.sql |
| 02 | 집계와 그룹화 | GROUP BY · HAVING · 조건부 집계(CASE+SUM) |
★☆☆ | recipes/02_group_by.sql |
| 03 | 윈도우 함수 | ROW_NUMBER · RANK · LAG/LEAD · 누적합 |
★★☆ | recipes/03_window_functions.sql |
| 04 | 날짜 다루기 | 일/주/월 버킷팅 · 날짜 채우기(gap fill) | ★★☆ | recipes/04_dates.sql |
| 05 | 코호트 리텐션 | 가입월 코호트 × 경과월 리텐션 매트릭스 | ★★★ | recipes/05_cohort_retention.sql |
| 06 | 퍼널 분석 | 단계별 전환율 · 이탈 구간 탐지 | ★★★ | recipes/06_funnel.sql |
| 07 | 피벗 / 언피벗 | 행↔열 변환 · 동적 피벗 | ★★☆ | recipes/07_pivot.sql |
| 08 | Top-N per group | 그룹별 상위 N개 (윈도우 + 필터) | ★★☆ | recipes/08_topn_per_group.sql |
난이도: ★☆☆ 입문 · ★★☆ 실무 · ★★★ 분석 패턴
아래 쿼리는 모두 합성 더미 데이터(
orders,events,users) 기준입니다. 방언은 PostgreSQL/MS-SQL 공통 표준 SQL을 기본으로 하고, 차이는 지원 DB 노트에 정리했습니다.
날짜별 매출을 집계하면서 누적 매출과 7일 이동평균을 한 번에 뽑는 패턴. 매출 추세를 볼 때 가장 자주 쓰는 조합입니다.
WITH daily AS (
SELECT
CAST(order_ts AS DATE) AS order_date,
SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY CAST(order_ts AS DATE)
)
SELECT
order_date,
revenue,
SUM(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue,
AVG(revenue) OVER (
ORDER BY order_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7d
FROM daily
ORDER BY order_date;핵심은 ROWS BETWEEN ... PRECEDING AND CURRENT ROW 프레임 절. 누적합은 UNBOUNDED PRECEDING, 이동평균은 6 PRECEDING(자신 포함 7행)으로 윈도우 폭만 바꿔 끼우면 됩니다.
"몇 월에 가입한 유저가 N개월 뒤에 얼마나 남아있나"를 보는 코호트 리텐션. 게임·이커머스·구독 어디서나 쓰는 분석 패턴입니다.
WITH first_month AS ( -- 유저별 가입(최초 활동) 월
SELECT
user_id,
MIN(DATE_TRUNC('month', event_ts)) AS cohort_month
FROM events
GROUP BY user_id
),
activity AS ( -- 유저별 활동 월 (중복 제거)
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_ts) AS active_month
FROM events
),
joined AS (
SELECT
f.cohort_month,
-- 가입월 대비 경과 개월 수
(EXTRACT(YEAR FROM a.active_month) - EXTRACT(YEAR FROM f.cohort_month)) * 12
+ (EXTRACT(MONTH FROM a.active_month) - EXTRACT(MONTH FROM f.cohort_month)) AS month_offset,
a.user_id
FROM first_month f
JOIN activity a ON a.user_id = f.user_id
)
SELECT
cohort_month,
month_offset,
COUNT(DISTINCT user_id) AS retained_users,
ROUND(
100.0 * COUNT(DISTINCT user_id)
/ MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_month)
, 1) AS retention_pct
FROM joined
GROUP BY cohort_month, month_offset
ORDER BY cohort_month, month_offset;month_offset = 0이 코호트 전체 크기(분모)가 되고, 같은 코호트의 OVER (PARTITION BY cohort_month) 최댓값으로 나눠 **리텐션 %**를 구합니다.
view → cart → checkout → purchase 4단계 퍼널에서 단계별 잔존 인원과 직전 단계 대비 전환율을 계산합니다.
WITH step_users AS ( -- 단계별 도달 유저 (중복 제거)
SELECT step_name, COUNT(DISTINCT user_id) AS users
FROM events
WHERE step_name IN ('view', 'cart', 'checkout', 'purchase')
GROUP BY step_name
),
ordered AS ( -- 퍼널 순서 부여
SELECT
step_name,
users,
CASE step_name
WHEN 'view' THEN 1
WHEN 'cart' THEN 2
WHEN 'checkout' THEN 3
WHEN 'purchase' THEN 4
END AS step_order
FROM step_users
)
SELECT
step_order,
step_name,
users,
ROUND(100.0 * users
/ FIRST_VALUE(users) OVER (ORDER BY step_order) -- 최초 단계 대비
, 1) AS pct_of_top,
ROUND(100.0 * users
/ LAG(users) OVER (ORDER BY step_order) -- 직전 단계 대비
, 1) AS step_conversion_pct
FROM ordered
ORDER BY step_order;FIRST_VALUE로 전체 전환율(맨 윗단계 대비)을, LAG로 단계 전환율(직전 단계 대비)을 동시에 봅니다. step_conversion_pct가 유독 낮은 구간이 이탈 병목입니다.
레시피는 표준 SQL을 기본으로 하되, 방언 차이가 잦은 지점만 아래에 정리했습니다.
| 기능 | MS-SQL (T-SQL) | MySQL (8.0+) | PostgreSQL |
|---|---|---|---|
| 월 절단 | DATEFROMPARTS(YEAR(d), MONTH(d), 1) |
DATE_FORMAT(d, '%Y-%m-01') |
DATE_TRUNC('month', d) |
| 날짜 차이(월) | DATEDIFF(MONTH, a, b) |
TIMESTAMPDIFF(MONTH, a, b) |
(EXTRACT(...) ...) * 12 + ... |
| 문자열 결합 | CONCAT() / + |
CONCAT() |
CONCAT() / || |
| 상위 N | SELECT TOP (N) ... |
LIMIT N |
LIMIT N |
| 반올림 | ROUND(x, 1) |
ROUND(x, 1) |
ROUND(x::numeric, 1) |
| 현재시각 | GETDATE() |
NOW() |
NOW() |
| 피벗 | PIVOT (...) 구문 |
CASE+SUM 수동 |
crosstab() 또는 CASE+SUM |
| 불리언 집계 | SUM(CASE WHEN ... THEN 1 ELSE 0 END) |
동일 | COUNT(*) FILTER (WHERE ...) 가능 |
팁:
DATE_TRUNC/EXTRACT는 PostgreSQL 표기입니다. MS-SQL에서는DATEFROMPARTS+DATEDIFF, MySQL에서는DATE_FORMAT+TIMESTAMPDIFF로 바꿔 끼우세요. 윈도우 함수(ROW_NUMBER/LAG/FIRST_VALUE/누적 프레임)는 세 DB 모두 동일하게 동작합니다.
레시피는 아래 3개 더미 테이블을 기준으로 작성했습니다. (모두 합성 데이터)
-- 유저
CREATE TABLE users (
user_id INT PRIMARY KEY,
signup_ts TIMESTAMP,
country VARCHAR(2), -- 'KR', 'US', 'SG' ...
channel VARCHAR(20) -- 'organic', 'paid', 'referral'
);
-- 주문 (이커머스 도메인)
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
order_ts TIMESTAMP,
amount DECIMAL(12, 2),
status VARCHAR(10) -- 'paid', 'refunded', 'cancelled'
);
-- 이벤트 (퍼널/코호트 도메인)
CREATE TABLE events (
event_id BIGINT PRIMARY KEY,
user_id INT,
event_ts TIMESTAMP,
step_name VARCHAR(20), -- 'view', 'cart', 'checkout', 'purchase'
session_id VARCHAR(36)
);seed/ 폴더에 세 DB용 INSERT 스크립트(소량 더미)가 들어 있습니다. 클론 후 본인 환경에 맞는 스크립트를 실행하면 모든 레시피를 바로 돌려볼 수 있습니다.
# 예시 (PostgreSQL)
psql -d recipes -f seed/seed_postgres.sql
psql -d recipes -f recipes/05_cohort_retention.sql- 이 저장소를 클론합니다.
- 본인 DB에 샘플 테이블을 만들고
seed/스크립트로 더미 데이터를 넣습니다. recipes/안의.sql파일을 열어 주석 → 쿼리 → 방언 노트 순서로 읽습니다.- 필요한 패턴을 복사해 본인 테이블명/컬럼명에 맞게 바꿔 씁니다.
모든 쿼리는 학습용 합성 데이터 기준입니다. 실제 운영 데이터에 적용할 때는 인덱스·실행계획·데이터량을 반드시 함께 검토하세요.
MIT License — 자유롭게 복사·수정·재배포하셔도 됩니다. 학습과 실무 양쪽에 편하게 쓰세요.
자매 저장소도 함께 둘러보세요 → game-data-recipes · dart-xbrl-parser · python-automation-100 · related_kws