SQL 競合分析 — シェア・成長率・ランキングの基礎

WITH base AS (
  SELECT
    company_name,
    fiscal_year,
    revenue,
    LAG(revenue) OVER (
      PARTITION BY company_name    -- 企業ごとに独立したウィンドウ
      ORDER BY     fiscal_year     -- 年度昇順で1行前を参照
    ) AS prev_revenue               -- 各社先頭年（2022）はNULL
  FROM   annual_revenue
)
SELECT
  company_name,
  fiscal_year,
  revenue,
  prev_revenue,
  ROUND(
    (revenue - prev_revenue) * 100.0 /
    NULLIF(prev_revenue, 0),         -- prev=0のときNULLを返しゼロ除算を防ぐ
    1
  ) AS yoy_pct
FROM   base
ORDER BY company_name, fiscal_year;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE base    → CTE を定義（LAG でウィンドウ関数を評価）
  2. 外側クエリ:
     FROM base    → 行を読み込む
     SELECT       → 列を評価（yoy_pct を整形）
     ORDER BY     → 並び替えて出力
*/

SELECT
  category,
  company_name,
  avg_score,
  review_count,
  RANK() OVER (
    PARTITION BY category        -- カテゴリごとに1位からリセット
    ORDER BY     avg_score DESC  -- スコア高い順に1,2,3,...を付与
  ) AS score_rank
FROM   competitor_scores
ORDER BY category, score_rank;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM competitor_scores  → 行を読み込む
  2. RANK() OVER (...)       → ウィンドウ関数を評価（行数は保持）
  3. SELECT                  → 列を評価
  4. ORDER BY                → 並び替えて出力
*/

SELECT
  company_name,
  MAX(CASE WHEN kpi_name = '売上高(億円)'    THEN kpi_value END) AS revenue_100m,  -- 縦持ちを横持ちにピボット
  MAX(CASE WHEN kpi_name = '営業利益率(%)'   THEN kpi_value END) AS op_margin_pct,
  MAX(CASE WHEN kpi_name = '顧客獲得数(万件)' THEN kpi_value END) AS new_customers_10k
FROM   competitor_kpi
GROUP BY company_name
ORDER BY revenue_100m DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. FROM competitor_kpi         → 縦持ちデータを読み込む
  2. GROUP BY company_name       → 会社ごとにグループ化
  3. CASE + MAX(...)             → ピボット（横持ちに変換）
  4. SELECT                      → 列を選択
  5. ORDER BY revenue_100m DESC  → 並び替えて出力
  */

SELECT
  product_name,
  company_name,
  monthly_price,
  price_quartile,
  CASE price_quartile
    WHEN 1 THEN '低価格帯'     -- Q1: 最も安いグループ
    WHEN 2 THEN '中低価格帯'
    WHEN 3 THEN '中高価格帯'
    WHEN 4 THEN '高価格帯'    -- Q4: 最も高いグループ
  END AS price_segment
FROM (
  SELECT
    product_name,
    company_name,
    monthly_price,
    NTILE(4) OVER (
      ORDER BY monthly_price  -- 価格昇順で4等分に振り分け
    ) AS price_quartile
  FROM   competitor_products
) sub
ORDER BY monthly_price;

/*
  実行順序（SQLの論理的な評価順）:
  1. サブクエリ sub
  2. 外側クエリ
  */