SQL KPI分析 — 移動平均・構成比・チャーン率の基礎

SELECT
  category,
  sales,
  ROUND(sales * 100.0 / SUM(sales) OVER (), 2) AS sales_pct,  -- 構成比 = 各行 ÷ 全体合計
  ROUND(
    SUM(sales) OVER (ORDER BY sales DESC) * 100.0      -- 累計（ランニング合計）
    / SUM(sales) OVER (), 2
  ) AS cum_pct                                              -- 累計構成比
FROM category_sales
ORDER BY sales DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM category_sales                    → 行を読込
  2. SUM(sales) OVER ()                     → 総合計を全行に付与
  3. SUM(sales) OVER (ORDER BY sales DESC)  → 売上降順での累計を計算
  4. SELECT で比率を計算                          → 構成比を算出
  5. ORDER BY sales DESC                    → 売上降順で出力
  */

SELECT
  category,
  product,
  sales,
  RANK()       OVER (PARTITION BY category ORDER BY sales DESC)          AS rnk,        -- 同順位あり・次を飛ばす
  DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC)          AS dense_rnk,  -- 同順位あり・飛ばさない
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC, product) AS row_num     -- 強制連番（タイ崩しに第2キー）
FROM product_sales
ORDER BY category, sales DESC, product;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM product_sales          → 行を読込
  2. PARTITION BY category       → カテゴリで分割
  3. ORDER BY sales DESC（窓内）     → 各パーティション内で売上降順
  4. RANK/DENSE_RANK/ROW_NUMBER  → 順位を振る
  5. ORDER BY                    → 最終表示順を整える
  */

SELECT
  user_id,
  COUNT(*) FILTER (WHERE status = 'completed')   AS completed_orders,  -- 完了注文だけ数える
  COALESCE(SUM(amount) FILTER (WHERE status = 'completed'), 0) AS completed_amount  -- 完了金額だけ合計（該当なしは0）
FROM orders
GROUP BY user_id
HAVING COUNT(*) FILTER (WHERE status = 'completed') >= 2   -- 集計後にグループを絞る
ORDER BY completed_amount DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM orders                                   → 8行読込
  2. GROUP BY user_id                              → U1,U2,U3,U4 の4グループ
  3. 集計（FILTER で completed のみ対象）          → U1:2/3500, U2:1/500, U3:3/6500, U4:0/0
  4. HAVING completed_orders >= 2                  → U2(1)・U4(0) を除外
  5. SELECT / ORDER BY completed_amount DESC       → 金額降順で出力
*/

SELECT prev.user_id                -- 3月にいて4月にいない＝離脱ユーザー
FROM      monthly_active AS prev
LEFT JOIN monthly_active AS curr
  ON  curr.user_id = prev.user_id
  AND curr.month   = '2024-04'     -- 右テーブルの絞り込みは ON 句に置く
WHERE prev.month = '2024-03'
  AND curr.user_id IS NULL         -- 結合相手なし＝4月に非アクティブ
ORDER BY prev.user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM prev LEFT JOIN curr      → prev に翌月を突き合わせ（相手なしはNULL保持）
  2. WHERE prev.month で基準月     → 基準月のユーザーに限定
  3. AND curr.user_id IS NULL      → 翌月に相手がない行＝離脱だけ残す
  4. SELECT prev.user_id / ORDER BY → 昇順で出力

  -- チャーン率（おまけ）:
  --   COUNT(*) FILTER (WHERE curr.user_id IS NULL) * 100.0 / COUNT(*)
  --   = 2 / 4 * 100 = 50.00 （分母は3月アクティブ数）
*/