SQL CTE・WITH句 — 複数CTE・累計・RFMの応用

QUESTION 6

CTE + CROSS JOIN + COALESCE — 欠損月を0埋めして完全マトリックスを作る

CTECROSS JOINCOALESCELEFT JOIN0埋めAPI

前提知識 ▼

実データには「売上がなかった月・商品の組み合わせ」が欠損します。ダッシュボードAPIでは全月×全商品の完全マトリックス（売上なしは0）が必要なことが多く、CROSS JOIN + LEFT JOIN + COALESCE で作ります。

-- CROSS JOIN: 2テーブルの全組み合わせ（直積）を生成
SELECT m.month, p.product_id
FROM   months   m
CROSS JOIN products p
-- months 3行 × products 4行 = 12行の全組み合わせ

-- COALESCE: 最初のNULLでない値を返す関数
COALESCE(sales, 0)   -- sales が NULL なら 0 を返す（売上なしを0で置換）

なぜ LEFT JOIN が必要か：CROSS JOINで「全組み合わせ」を作り、実績テーブルを LEFT JOIN すると「売上がなかった組み合わせ」の売上列が NULL になります。その NULL を COALESCE で 0 に変換して完成です。

問題 ▼

以下の sales テーブルと products テーブルから、2024-01〜2024-03 の全月 × 全商品の売上マトリックスを作成してください。

売上がない組み合わせは 0 で埋めること。月一覧は CTE で生成してください。

使用テーブル

▸ sales

sale_month	product_id	amount
2024-01	P01	30000
2024-01	P02	45000
2024-02	P01	50000
2024-03	P02	20000
2024-03	P03	35000

▸ products

product_id	product_name
P01	りんご
P02	バナナ
P03	みかん

期待出力 ▼

sale_month	product_id	product_name	total_sales
2024-01	P01	りんご	30000
2024-01	P02	バナナ	45000
2024-01	P03	みかん	0
2024-02	P01	りんご	50000
2024-02	P02	バナナ	0
2024-02	P03	みかん	0
2024-03	P01	りんご	0
2024-03	P02	バナナ	20000
2024-03	P03	みかん	35000

模範解答コード ▼

WITH
months AS (                        -- ① 対象月の一覧を CTE で定義（実データがなくても全月を確保）
  SELECT '2024-01' AS sale_month   -- SELECT: 固定の文字列を取得し、列名をつける
  UNION ALL SELECT '2024-02'       -- UNION ALL: 前の結果に縦に結合していく
  UNION ALL SELECT '2024-03'
),
all_combinations AS (              -- ② 全月 × 全商品 の組み合わせ（CROSS JOIN: 3月 × 3商品 = 9行）
  SELECT
    m.sale_month,
    p.product_id,
    p.product_name
  FROM       months   m            -- FROM: 月テーブル（3行）
  CROSS JOIN products p            -- CROSS JOIN: 結合条件なしで、全行×全行のすべての組み合わせ(直積)を作る
),
sales_agg AS (                     -- ③ 実績テーブル sales を月×商品でグループ集計しておく
  SELECT
    sale_month,
    product_id,
    SUM(amount) AS total_sales     -- SUM: 同月同商品が複数行あれば合算
  FROM     sales
  GROUP BY sale_month, product_id  -- GROUP BY: 月と商品のペアでグループ化
)

-- ④ 全組み合わせ に 実績を LEFT JOIN して0埋め
SELECT
  c.sale_month,
  c.product_id,
  c.product_name,
  COALESCE(s.total_sales, 0) AS total_sales  -- COALESCE(値, 代替値): NULLなら代替値(0)を返す
FROM       all_combinations c            -- FROM: 全組み合わせを基準（左テーブル）
LEFT JOIN  sales_agg        s            -- LEFT JOIN: 右(s)にデータがなくても、左(c)の行をそのまま残す結合
        ON  c.sale_month  = s.sale_month -- ON: 結合条件1（月が一致）
       AND  c.product_id  = s.product_id -- AND: 結合条件2（商品も一致）
ORDER BY c.sale_month, c.product_id;     -- ORDER BY: 月の昇順 → 商品IDの昇順で並べる

/*
  実行順序:
  1. CTE months                       → 月一覧を生成
  2. CTE all_combinations             → CROSS JOIN で全組み合わせ
  3. CTE sales_agg                    → sales を集計
  4. 本体 LEFT JOIN                     → 組み合わせに実績を結合（未実績はNULL）
  5. COALESCE(total_sales, 0)         → NULL を 0 に変換
  6. ORDER BY sale_month, product_id  → 整列して出力
  */

解説（テーブル変化・ポイント） ▼

WITH
months AS (
  SELECT '2024-01' AS sale_month
  UNION ALL SELECT '2024-02'
  UNION ALL SELECT '2024-03'
),
all_combinations AS (
  SELECT m.sale_month, p.product_id, p.product_name
  FROM months m
  CROSS JOIN products p
),
sales_agg AS (
  SELECT sale_month, product_id,
    SUM(amount) AS total_sales
  FROM sales
  GROUP BY sale_month, product_id
)
SELECT
  c.sale_month, c.product_id, c.product_name,
  COALESCE(s.total_sales, 0) AS total_sales
FROM all_combinations c
LEFT JOIN sales_agg s
  ON c.sale_month = s.sale_month
 AND c.product_id = s.product_id
ORDER BY c.sale_month, c.product_id;

LEGEND ▼

データ取得・読込対象

① CTE: months / products ▼

months (3行) と products (3行) を準備months CTE は固定の月リスト（3行）を生成します。products テーブルは3商品（3行）です。この2つを CROSS JOIN します。

1 / 6

▸ CTE months

sale_month
2024-01
2024-02
2024-03

⋈

▸ products

product_id	product_name
P01	りんご
P02	バナナ
P03	みかん

左: 3行 / 右: 3行

学習ポイント

0埋めパターンの実務価値：折れ線グラフAPIで「データがない月は0」で返さないとフロントエンドのグラフが途切れます。CTE+CROSS JOIN+LEFT JOIN+COALESCEの組み合わせは「完全マトリックス生成」の王道パターンです。

月一覧の動的生成：PostgreSQLでは generate_series('2024-01-01'::date, '2024-03-01'::date, '1 month') で連続した月を動的生成できます。APIでパラメータを受け取る場合に有効です。

アンチパターン

COALESCE を忘れる：LEFT JOINだけでは実績なし行のtotal_salesはNULLのままです。APIでNULLを返すとフロントエンドが undefined として扱い描画が崩れます。

実務コラム

フロントエンド向けデータの「欠損埋め」：完全マトリックスを作成して0で埋める処理は、フロントエンドエンジニアから最も感謝される配慮の一つです。APIが「売上があった月・商品」のデータしか返さないと、グラフ描画ライブラリ（Chart.jsなど）でX軸の日付が飛んだりズレたりするバグの温床になります。サーバー側で欠損を0埋めした完全な時系列データを返すのが、堅牢なAPI設計の基本です。

QUESTION 7

CTEでロジック分解 — 複雑な条件フィルタをステップに分けて可読性を上げる

複数CTEHAVING集計フィルタセグメントAPICTE

前提知識 ▼

実務では「2回以上購入し、合計購入額が50,000円以上で、最終購入日が特定日以降」といった複合条件のAPIが求められます。これを1クエリに詰め込むと保守困難になります。CTEでステップを分解すると各条件を独立してテスト・修正できます。

WITH filtered AS (               -- ① 行単位の絞り込みは WHERE
  SELECT *
  FROM   table_name
  WHERE  date_col >= DATE '2024-01-01'
), grouped AS (                    -- ② グループ単位の絞り込みは HAVING
  SELECT key_col,
         COUNT(*)      AS cnt,
         SUM(num_col)  AS total
  FROM   filtered
  GROUP BY key_col
  HAVING COUNT(*) >= 2
)
SELECT * FROM grouped;

HAVING：GROUP BY後のグループに対してフィルタをかける句。COUNT(*) >= 2 のような集計関数を条件にする場合は必ず HAVING を使います。WHEREはグループ化前（行単位）、HAVINGはグループ化後（集計単位）です。

問題 ▼

以下の orders と users テーブルから、次の3つの条件をすべて満たすユーザーを抽出するAPIを、CTEを使ってステップに分解して作成してください。

条件①: 合計購入回数が2回以上
条件②: 合計購入額が50,000円以上
条件③: 最終購入日が 2024-03-01 以降

使用テーブル

▸ users

user_id	user_name
U01	Alice
U02	Bob
U03	Carol
U04	Dave
U05	Eve

▸ orders

order_id	user_id	amount	order_date
1	U01	20000	2024-01-15
2	U01	35000	2024-03-10
3	U02	60000	2024-02-20
4	U03	15000	2024-01-05
5	U03	25000	2024-03-22
6	U04	80000	2024-03-01
7	U04	10000	2024-04-05
8	U05	12000	2024-02-14

期待出力 ▼

user_id	user_name	order_count	total_amount	last_order_date
U01	Alice	2	55000	2024-03-10
U04	Dave	2	90000	2024-04-05

模範解答コード ▼

WITH
user_stats AS (                          -- ① ユーザーごとに購入統計を集計する（3条件判定の基礎データ）
  SELECT                                 -- SELECT: 取得する列
    user_id,
    COUNT(*)        AS order_count,      -- COUNT(*): レコードの行数 = 購入回数
    SUM(amount)     AS total_amount,     -- SUM(): 金額列の合計 = 合計購入額
    MAX(order_date) AS last_order_date   -- MAX(): 日付の最大値 = 一番新しい購入日
  FROM     orders                        -- FROM: データ取得元テーブル
  GROUP BY user_id                       -- GROUP BY: ユーザーごとにまとめて集計
),
qualified_users AS (                     -- ② 3つの条件をすべて適用してフィルタ
  SELECT *                               -- SELECT *: テーブルの全列を取得
  FROM   user_stats                      -- FROM: 上で作ったCTEを参照
  WHERE                                  -- WHERE: 以下の条件をすべて満たす行だけ残す
    order_count     >= 2                 -- 条件①: 購入回数が2回以上
    AND total_amount    >= 50000         -- AND: かつ。条件②: 合計金額が5万円以上
    AND last_order_date >= '2024-03-01'  -- AND: かつ。条件③: 最終購入日が2024-03-01以降
)

-- ③ 条件を満たしたユーザーに users テーブルから名前を付加して返す
SELECT
  q.user_id,
  u.user_name,                    -- users テーブルから名前を取得
  q.order_count,
  q.total_amount,
  q.last_order_date
FROM       qualified_users q      -- FROM: 条件をクリアしたユーザーのテーブル
INNER JOIN users           u      -- INNER JOIN: usersテーブルと結合（両方にある行だけ残す）
        ON q.user_id = u.user_id  -- ON: user_idが一致するものを結びつける
ORDER BY   q.user_id;   -- 期待表どおりユーザーID昇順で並べる

/*
  実行順序:
  1. CTE user_stats                 → ユーザーごとに COUNT/SUM/MAX を集計
  2. CTE qualified_users            → 集計値で絞り込み
  3. 本体 INNER JOIN users            → user_id で結合
  4. 本体 ORDER BY user_id            → ユーザーID昇順で出力
  */

解説（テーブル変化・ポイント） ▼

WITH
user_stats AS (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    MAX(order_date) AS last_order_date
  FROM orders
  GROUP BY user_id
),
qualified_users AS (
  SELECT *
  FROM user_stats
  WHERE
    order_count     >= 2
    AND total_amount    >= 50000
    AND last_order_date >= '2024-03-01'
)
SELECT
  q.user_id, u.user_name,
  q.order_count, q.total_amount, q.last_order_date
FROM qualified_users q
INNER JOIN users u
  ON q.user_id = u.user_id
ORDER BY q.user_id;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

orders テーブルから集計元のデータを確認orders テーブルにはユーザーごとの購入履歴が記録されています。これをユーザー単位（user_id）でグループ化して、購入回数・合計額・最終購入日を集計します。

1 / 5

order_id	user_id	amount	order_date
1	U01	20,000	2024-01-15
2	U01	35,000	2024-03-10
3	U02	60,000	2024-02-20
4	U03	15,000	2024-01-05
5	U03	25,000	2024-03-22
6	U04	80,000	2024-03-01
7	U04	10,000	2024-04-05
8	U05	12,000	2024-02-14

全 8行（集計前）

学習ポイント

CTEのデバッグ手法：CTEを個別に SELECT * FROM user_stats として実行することで中間結果を確認できます。「なぜこのユーザーが最終結果に入らないか」をステップごとに検証できます。

MAX(order_date)：MAX は数値だけでなく日付にも使えます。日付は辞書順で大小比較されるため、MAX(order_date) で最新日付が取得できます。

アンチパターン

集計関数を WHERE に書く：WHERE COUNT(*) >= 2 はエラー。GROUP BY 後の集計値でフィルタするには HAVING か、CTEを使った外側の WHERE を使ってください。

実務コラム

CRMの高度なセグメント検索API：マーケティング部門から「X回以上購入し、Y円以上使い、かつ直近1ヶ月以内にアクセスしたユーザーリストを出して」というような複合条件の要求は日常茶飯事です。条件ごとにCTEを分ける、または集計CTEを1つ作って外側でWHERE条件を並べる設計にしておくと、将来「平均購入単価がZ円以上」という新しい条件が追加された際のコード改修が劇的に簡単になり、保守性が高まります。

QUESTION 8

CTE + SUM OVER — 累計売上（ランニングトータル）を計算する

SUM OVERCTE累計ROWS BETWEEN進捗API

前提知識 ▼

SUM() OVER (ORDER BY ...) はウィンドウ関数で、現在行までの合計（累計）を計算します。月ごとの売上推移だけでなく「年間目標に対して今どこにいるか」を返す進捗APIに使います。

SUM(sales) OVER (
  ORDER BY order_month              -- 月順に並べてから累計を計算
  ROWS BETWEEN UNBOUNDED PRECEDING  -- ROWS BETWEEN: 集計対象の範囲を指定
    AND CURRENT ROW                  -- 「先頭行から現在行まで」= 累計
) AS cumulative_sales

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW：「先頭行から現在行まで」を意味するフレーム指定です。ORDER BY だけでも累計は計算されますが、明示的に書くと意図が明確になります。UNBOUNDED は「制限なし（端まで）」という意味です。

問題 ▼

以下の monthly_sales テーブルから、月ごとの売上・累計売上・年間目標（800,000円）に対する達成率（%）を計算するAPIを作成してください。

CTEで月次集計を行い、外側のSELECTで累計と達成率を計算してください。

使用テーブル

▸ monthly_sales

order_month	sales
2024-01	95000
2024-02	120000
2024-03	108000
2024-04	145000
2024-05	132000
2024-06	160000

期待出力 ▼

order_month	monthly_sales	cumulative_sales	achievement_rate
2024-01	95000	95000	11.9
2024-02	120000	215000	26.9
2024-03	108000	323000	40.4
2024-04	145000	468000	58.5
2024-05	132000	600000	75.0
2024-06	160000	760000	95.0

模範解答コード ▼

WITH monthly_agg AS (

  -- ① 月ごとの売上を集計する
  SELECT                         -- SELECT: 取得列を指定
    order_month,
    SUM(sales) AS monthly_sales  -- SUM(): 同月の売上を合算
  FROM     monthly_sales         -- FROM: 元テーブル
  GROUP BY order_month           -- GROUP BY: 月ごとにグループ化

)

-- ② 累計売上と達成率を計算する
SELECT
  order_month,
  monthly_sales,
  SUM(monthly_sales) OVER (           -- SUM() OVER(...): ウィンドウ関数。行をまとめずに(GROUP BYせず)累計などを計算する
    ORDER BY order_month              -- ORDER BY: 月の昇順に並べてから累積していく
    ROWS BETWEEN UNBOUNDED PRECEDING  -- ROWS BETWEEN: 計算対象の範囲を指定。UNBOUNDED PRECEDING は「最初の行から」
      AND CURRENT ROW                 -- CURRENT ROW: 「現在の行まで」。つまり「最初から今までの累計」
  ) AS cumulative_sales,              -- AS: 列に cumulative_sales という別名をつける
  ROUND(                                 -- 達成率 = 累計 ÷ 年間目標 × 100
    SUM(monthly_sales) OVER (            -- 上と同じ累計計算
      ORDER BY order_month
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * 100.0 / 800000                   -- 100.0を掛けて%にし、年間目標(800000)で割る
  , 1) AS achievement_rate               -- 小数点第1位に丸め、別名をつける

FROM     monthly_agg   -- FROM: CTEを参照
ORDER BY order_month;  -- ORDER BY: 月の昇順で結果を並べる

/*
  実行順序:
  1. CTE monthly_agg          → 月次売上を集計
  2. 外側 SUM() OVER(...)       → 累計を計算
  3. 外側 ROUND(...)            → 達成率を計算
  4. 外側 ORDER BY order_month  → 整列して出力
  */

解説（テーブル変化・ポイント） ▼

WITH monthly_agg AS (
  SELECT
    order_month,
    SUM(sales) AS monthly_sales
  FROM monthly_sales
  GROUP BY order_month
)
SELECT
  order_month, monthly_sales,
  SUM(monthly_sales) OVER (
    ORDER BY order_month
    ROWS BETWEEN UNBOUNDED PRECEDING
      AND CURRENT ROW
  ) AS cumulative_sales,
  ROUND(
    SUM(monthly_sales) OVER (
      ORDER BY order_month
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) * 100.0 / 800000
  , 1) AS achievement_rate
FROM     monthly_agg
ORDER BY order_month;

LEGEND ▼

データ取得・読込対象

① FROM monthly_sales ▼

monthly_sales テーブルを確認月ごとの売上が記録されたテーブルです。今回はすでに月ごとに1行になっていますが、複数行ある場合を想定して CTE で安全にグループ化（GROUP BY）します。

1 / 4

order_month	sales
2024-01	95,000
2024-02	120,000
2024-03	108,000
2024-04	145,000
2024-05	132,000
2024-06	160,000

全 6行

学習ポイント

累計の実務用途：「今月末時点での年間売上進捗ダッシュボード」「ユーザーの生涯購入額（LTV）の累積推移」「キャンペーン期間中の累積申込数」など、時系列の進捗を見せるAPIに必須です。

なぜCTEで集計してから累計を計算するか：同じ月に複数行ある場合、先に GROUP BY で月次合計を出してからウィンドウ関数で累計を計算しないと、行単位で累計されてしまいます。CTE→SUM OVER の2ステップが安全です。

アンチパターン

ROWS BETWEEN を省略すると意図しない範囲になることがある：DBによっては ORDER BY だけ指定した場合のデフォルトフレームが「UNBOUNDED PRECEDING AND CURRENT ROW」でない場合があります。明示的に書くことで意図を明確にし、DB間の差異を防ぎます。

実務コラム

KPI目標のバーンアップチャート：ランニングトータルの計算は「月間目標達成率」や「売上の進捗ゲージ」を表示するダッシュボードAPIで必須の処理です。月末にかけて数字がどう積み上がったかを可視化するのに役立ちます。また、ECサイトでは「ユーザーの生涯購入額（LTV）が特定の閾値（例: 累計10万円）を超えた瞬間にVIP会員に昇格させる」といったバッチ処理の判定ロジックとしても多用されます。

QUESTION 9

CTE + LEAD — 次回購入日と購入間隔を計算してチャーン予測に使う

LEADCTE購入間隔チャーン分析LTV API

前提知識 ▼

LEAD(col, n) は LAG の逆で、現在行から n 行後の値を返します。「次の購入はいつか」「購入から次回購入まで何日かかったか」を計算するのに使います。

LEAD(order_date, 1) OVER (
  PARTITION BY user_id    -- ユーザーごとに独立して「次の行」を参照
  ORDER BY     order_date  -- 日付順に並べた次の行の order_date を返す
)
-- 最後の購入行（次がない行）は NULL になる

日付の差分計算：PostgreSQL では date2 - date1 で日数差を INTEGER で取得できます。BigQuery では DATE_DIFF(date2, date1, DAY) を使います。NULLが含まれる日付差はNULLになります（NULL伝播）。

問題 ▼

以下の purchase_log テーブルから、各ユーザー・各購入ごとの次回購入日と購入間隔（日数）を計算してください。

最後の購入（次回購入なし）は next_date を NULL、days_to_next も NULL で出力してください。

使用テーブル

▸ purchase_log

log_id	user_id	order_date
1	U01	2024-01-10
2	U01	2024-02-15
3	U01	2024-04-01
4	U02	2024-01-20
5	U02	2024-03-05
6	U03	2024-02-28

期待出力 ▼

user_id	order_date	next_date	days_to_next
U01	2024-01-10	2024-02-15	36
U01	2024-02-15	2024-04-01	46
U01	2024-04-01	NULL	NULL
U02	2024-01-20	2024-03-05	45
U02	2024-03-05	NULL	NULL
U03	2024-02-28	NULL	NULL

模範解答コード ▼

WITH purchase_with_next AS (

  -- ① LEAD で「次の購入日」を各行に追加する
  SELECT                        -- SELECT: 取得列
    user_id,
    order_date,
    LEAD(order_date, 1) OVER (  -- LEAD(列, n): n行後（未来）の値を取得（なければNULL）
      PARTITION BY user_id      -- PARTITION BY: ユーザーごとに区切って独立して処理する
      ORDER BY     order_date   -- ORDER BY: 日付の昇順に並べて「次」を定義する
    ) AS next_date              -- AS: 別名をつける
  FROM purchase_log             -- FROM: 元テーブル

)

-- ② 次回購入日が確定したので日数差（購入間隔）を計算する
SELECT
  user_id,
  order_date,
  next_date,                   -- 日付同士の引き算で日数差を計算
  next_date - order_date       -- 次の購入日 - 今回の購入日 = 間隔日数
    AS days_to_next            -- ※ next_dateがNULLなら、結果の計算もNULLになる（NULLの伝播）
FROM     purchase_with_next    -- FROM: CTEを参照
ORDER BY user_id, order_date;  -- ORDER BY: ユーザーIDの昇順 → 日付の昇順 で並べる

/*
  実行順序:
  1. CTE内 FROM purchase_log             → 全行読み込み
  2. CTE内 LEAD(order_date,1) OVER(...)  → 各行に次の購入日を付与
  3. 外側 FROM purchase_with_next         → CTE結果を参照
  4. 外側 next_date - order_date          → 日数差を計算
  5. 外側 ORDER BY user_id, order_date    → 整列して出力
  */

解説（テーブル変化・ポイント） ▼

WITH purchase_with_next AS (
  SELECT
    user_id, order_date,
    LEAD(order_date, 1) OVER (
      PARTITION BY user_id
      ORDER BY order_date
    ) AS next_date
  FROM purchase_log
)
SELECT
  user_id, order_date, next_date,
  next_date - order_date AS days_to_next
FROM     purchase_with_next
ORDER BY user_id, order_date;

LEGEND ▼

データ取得・読込対象

① FROM (入力) ▼

FROM purchase_log — PARTITION BY user_id でグループを確認purchase_log の全6行をユーザー別にグループ確認します。LEAD は PARTITION BY user_id で各ユーザー内に閉じた処理になります。ユーザーをまたいで「次の行」を参照するバグを防ぐ鍵です。

1 / 3

user_id	log_id	order_date
U01	1	2024-01-10
U01	2	2024-02-15
U01	3	2024-04-01
U02	4	2024-01-20
U02	5	2024-03-05
U03	6	2024-02-28

全 6行読込

学習ポイント

購入間隔の実務用途：「平均購入間隔を超えてもまだ購入がないユーザー = チャーンリスク」という分析に使います。購入間隔の平均・最大を別途CTEで集計し、「最後の購入日 + 平均間隔 < 今日」なら警告を出すAPIが構築できます。

NULL の扱い：最後の購入行は next_date が NULL なので days_to_next もNULL（NULL伝播）。これを使って「次回購入日がNULLのユーザー = 最後に購入してから間が空いているユーザー」として抽出できます。

アンチパターン

PARTITION BY を忘れる：PARTITION BY なしだと全行を1つのウィンドウとして扱うため、あるユーザーの最後の購入の「次の行」が別ユーザーの最初の購入になってしまいます。ユーザーごとに独立した分析には必ず PARTITION BY を指定してください。

実務コラム

チャーン（離脱）予測とLTV分析：LEADを使って「次回購入日」を算出し「購入間隔日数」を出す処理は、顧客分析の第一歩です。実務では、このクエリをさらに発展させ、「各ユーザーの平均購入間隔」を計算し、「最後の購入日から今日までの日数が、平均購入間隔の2倍以上空いているユーザー」をチャーン（離脱）の危険性が高いと判定して、自動で休眠復帰メールを送るシステムなどに組み込まれます。

QUESTION 10

複数CTE + CASE WHEN — RFMスコアでユーザーをセグメント分類する

複数CTECASE WHENRFM分析DATEDIFFセグメントAPI

前提知識 ▼

RFM分析は顧客をRecency（最終購入からの経過日数）・Frequency（購入回数）・Monetary（購入額）の3軸で評価し、セグメント分類するマーケティング手法です。複数CTEでステップを分けることで、各スコアの計算ロジックを独立させられます。

-- CASE WHEN: 条件分岐で値を変換する（if〜else ifのイメージ）
CASE
  WHEN days_since_activity < 14 THEN 5
  WHEN days_since_activity < 60 THEN 3
  ELSE                                1
END

基準日の設定：Recency は「今日から最終購入日までの日数」ですが、クエリの中では基準日を固定することが多いです（データが過去のものなので、CURRENT_DATE を使うとテスト時に結果が変わるため）。実務ではパラメータで受け取ります。

問題 ▼

以下の orders テーブルから、RFMスコア（各1〜3点）を計算してユーザーをセグメント（VIP / 一般 / 休眠）に分類するAPIを、複数CTEで作成してください。

基準日は 2024-04-01 とします。スコアリング基準：
R（Recency）: <30日=3, <90日=2, それ以外=1
F（Frequency）: ≥3回=3, ≥2回=2, それ以外=1
M（Monetary）: ≥100000=3, ≥50000=2, それ以外=1
セグメント: R+F+M が 8以上=VIP, 5以上=一般, それ以外=休眠

使用テーブル

▸ orders

order_id	user_id	amount	order_date
1	U01	30000	2024-01-10
2	U01	50000	2024-02-20
3	U01	40000	2024-03-25
4	U02	80000	2024-03-15
5	U02	60000	2024-03-28
6	U03	120000	2023-12-01
7	U04	15000	2024-03-30
8	U04	20000	2024-03-31
9	U04	18000	2024-04-01

期待出力 ▼

user_id	recency_days	frequency	monetary	r_score	f_score	m_score	total_score	segment
U01	7	3	120000	3	3	3	9	VIP
U02	4	2	140000	3	2	3	8	VIP
U04	0	3	53000	3	3	2	8	VIP
U03	122	1	120000	1	1	3	5	一般

模範解答コード ▼

WITH
rfm_raw AS (                                               -- ① ユーザーごとにRFMの生データを集計する
  SELECT                                                   -- SELECT: 取得列
    user_id,
    '2024-04-01'::date - MAX(order_date) AS recency_days,  -- Recency: 基準日 − 最終購入日
    COUNT(*)        AS frequency,  -- Frequency: 購入回数
    SUM(amount)     AS monetary  -- Monetary: 合計購入額
  FROM     orders                -- FROM: 元テーブル
  GROUP BY user_id               -- GROUP BY: ユーザーごとにまとめる
),
rfm_scored AS (                  -- ② RFMの生データを各スコア（1〜3点）に変換する
  SELECT
    user_id,
    recency_days,
    frequency,
    monetary,
    CASE                                        -- R スコア: 経過日数が少ないほど高い
      WHEN recency_days <  30 THEN 3             -- WHEN 条件 THEN 値: 30日未満なら3を返す
      WHEN recency_days <  90 THEN 2             -- 90日未満なら2を返す
      ELSE                       1              -- ELSE 値: どの条件にも当てはまらなければ1を返す
    END AS r_score,                             -- END: CASE文の終わり。ASで別名をつける
    CASE                                        -- F スコア: Frequency（購入回数が多いほど高スコア）
      WHEN frequency >= 3 THEN 3                -- 3回以上なら3を返す
      WHEN frequency >= 2 THEN 2                -- 2回以上なら2を返す
      ELSE                     1                -- それ以外は1を返す
    END AS f_score,
    CASE                                        -- M スコア: Monetary（購入額が多いほど高スコア）
      WHEN monetary >= 100000 THEN 3            -- 10万円以上なら3を返す
      WHEN monetary >=  50000 THEN 2            -- 5万円以上なら2を返す
      ELSE                         1            -- それ以外は1を返す
    END AS m_score
  FROM rfm_raw                                  -- FROM: 1つ目のCTEを参照
),
rfm_total AS (                                  -- ③ R+F+M の合計スコアを計算する
  SELECT
    *,                                          -- *: すべての列をそのまま取得
    r_score + f_score + m_score AS total_score  -- +: 足し算して合計スコアを算出
  FROM rfm_scored                               -- FROM: 2つ目のCTEを参照
)

-- ④ 合計スコアでセグメントを判定して出力する
SELECT
  user_id,
  recency_days,
  frequency,
  monetary,
  r_score,
  f_score,
  m_score,
  total_score,
  CASE                                -- CASE WHEN でスコアをセグメント名（文字列）に変換
    WHEN total_score >= 8 THEN 'VIP'  -- 合計8点以上は'VIP'文字列を返す
    WHEN total_score >= 5 THEN '一般'   -- 合計5点以上は'一般'を返す
    ELSE                       '休眠'   -- それ以外は'休眠'を返す
  END AS segment
FROM     rfm_total                    -- FROM: 3つ目のCTEを参照
ORDER BY total_score DESC, user_id;   -- ORDER BY ... DESC: 合計スコアの高い順 → ユーザーID順 に並べる

/*
  実行順序:
  1. CTE rfm_raw: FROM orders        → GROUP BY user_id
  2. CTE rfm_scored: FROM rfm_raw    → CASE WHEN でR/F/Mを各1〜3点に変換
  3. CTE rfm_total: FROM rfm_scored  → r_score+f_score+m_score で合計スコア計算
  4. 本体: FROM rfm_total              → CASE WHEN でセグメント判定
  */

解説（テーブル変化・ポイント） ▼

WITH
rfm_raw AS (
  SELECT user_id,
    '2024-04-01'::date - MAX(order_date) AS recency_days,
    COUNT(*) AS frequency,
    SUM(amount) AS monetary
  FROM orders
  GROUP BY user_id
),
rfm_scored AS (
  SELECT *, CASE
    WHEN recency_days < 30 THEN 3
    WHEN recency_days < 90 THEN 2
    ELSE 1 END AS r_score,
    CASE WHEN frequency >= 3 THEN 3
    WHEN frequency >= 2 THEN 2
    ELSE 1 END AS f_score,
    CASE WHEN monetary >= 100000 THEN 3
    WHEN monetary >= 50000 THEN 2
    ELSE 1 END AS m_score
  FROM rfm_raw
),
rfm_total AS (
  SELECT *, r_score + f_score + m_score AS total_score
  FROM rfm_scored
)
SELECT
  user_id, r_score, f_score, m_score, total_score,
  CASE
    WHEN total_score >= 8 THEN 'VIP'
    WHEN total_score >= 5 THEN '一般'
    ELSE '休眠' END AS segment
FROM     rfm_total
ORDER BY total_score DESC, user_id;

LEGEND ▼

データ取得・読込対象

① FROM orders ▼

orders テーブルから集計元のデータを確認orders テーブルにはユーザーごとの購入履歴が記録されています。ここからユーザーごとに「最新購入日」「購入回数」「合計購入額」を集約し、RFMの基礎データを作ります。

1 / 5

order_id	user_id	amount	order_date
1	U01	30,000	2024-01-10
2	U01	50,000	2024-02-20
3	U01	40,000	2024-03-25
4	U02	80,000	2024-03-15
5	U02	60,000	2024-03-28
6	U03	120,000	2023-12-01
7	U04	15,000	2024-03-30
8	U04	20,000	2024-03-31
9	U04	18,000	2024-04-01

全 9行（集計前）

学習ポイント

RFM分析の実務価値：「VIPユーザーに向けた特別キャンペーンAPI」「休眠ユーザーへの再購入促進メール配信リスト取得」など、マーケティング施策のターゲティングに直結します。スコアリング基準は事業ドメインに応じて変えられます。

CASE WHEN の評価順：CASE WHEN は上から順に評価し、最初に合致した THEN の値を返します。条件は排他的でなくてよい（例: <30 と <90 は重複するが先に <30 がチェックされるため問題なし）。

スコアリング基準の外部化：実務では閾値（30日、90日、100000円など）をハードコードせず、パラメータテーブルや設定ファイルから取得します。CTEを使うとこの「設定値CTE」を先頭に追加するだけで対応でき、保守性が高まります。

アンチパターン

CASE WHEN の ELSE を省略する：ELSE を書かないと、どの条件にも合致しない行は NULL になります。スコアがNULLになると total_score も NULL になり、セグメントも判定できなくなります。必ず ELSE でデフォルト値を設定してください。

基準日に CURRENT_DATE を使う：テスト・再現性のために基準日は固定値にするのが推奨です。CURRENT_DATEを使うと、同じSQLを別の日に実行すると結果が変わり、デバッグが困難になります。

実務コラム

顧客ターゲティングの自動化：RFM分析の結果を返すAPIは、マーケティングオートメーション（MA）ツールの根幹を担います。セグメントごとに異なる施策（VIPには限定イベント招待、休眠にはポイントプレゼントなど）を打ち出すためのリスト抽出に使われます。スコアリングの閾値（30日、90日、10万円など）は事業の成長フェーズによって見直されるため、実務ではCTEの中に数値を直書きするのではなく、パラメータとして外部から受け取る設計にするのがベストプラクティスです。