【SQL実践ドリル第10回】総合演習 ― 実務想定の複合問題10問

Posted at 2026-04-01

このシリーズでは共通のサンプルデータベースを使用します。初回（第1回）でCREATE TABLE文を掲載しています。

はじめに

最終回となる第10回では、第1回〜第9回で学んだ内容を組み合わせた 実務想定の総合問題 に挑戦します。すべての問題が JOIN + 集約 + サブクエリまたはウィンドウ関数を必要とする複合問題です。

実務では「この売上データからどんな分析ができるか」を自分で考える力が求められます。ぜひ1問ずつ手を動かして取り組んでください。

問題 1 ⭐⭐（応用）月別の売上レポート

ビジネス背景: 経営会議で毎月の売上推移を報告する必要があります。月ごとの注文数、売上合計、前月比を一覧で出してください。

要件:

年月、注文数、売上合計、前月の売上合計、前月比（%）を表示
前月比は小数第1位まで表示
注文がない月は結果に含まれなくてよい

期待結果:

+---------+-------------+------------+------------+-----------+
| 年月    | order_count | total_sales| prev_sales | mom_ratio |
+---------+-------------+------------+------------+-----------+
| 2024-07 |           3 |     593100 |       NULL |      NULL |
| 2024-08 |           2 |     283000 |     593100 |      47.7 |
| 2024-09 |           2 |     277500 |     283000 |      98.1 |
| 2024-10 |           2 |     319500 |     277500 |     115.1 |
| 2024-11 |           1 |     295000 |     319500 |      92.3 |
+---------+-------------+------------+------------+-----------+

模範解答

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(o.order_date, '%Y-%m') AS 年月,
        COUNT(DISTINCT o.order_id) AS order_count,
        SUM(od.quantity * p.price) AS total_sales
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT
    年月,
    order_count,
    total_sales,
    LAG(total_sales) OVER (ORDER BY 年月) AS prev_sales,
    ROUND(
        total_sales / LAG(total_sales) OVER (ORDER BY 年月) * 100,
        1
    ) AS mom_ratio
FROM monthly_sales
ORDER BY 年月;

解説:

CTE monthly_sales で月ごとの注文数と売上合計を集計する
LAG() ウィンドウ関数で1つ前の行（＝前月）の売上を取得する
前月比は 当月売上 / 前月売上 * 100 で計算する

注意: COUNT(DISTINCT o.order_id) を使っているのは、1つの注文に複数の明細行があるため、COUNT(*) だと明細行数を数えてしまうからです。

検証:

各月の注文と売上:

2024-07: 注文1,2,3
- 注文1: 198000×2 + 3500×5 = 396000 + 17500 = 413500
- 注文2: 45000×1 + 12000×3 = 45000 + 36000 = 81000
- 注文3: 89000×1 + 4800×2 = 89000 + 9600 = 98600
- 合計: 593100（注文数: 3）
2024-08: 注文4,5
- 注文4: 198000×1 + 6500×4 = 198000 + 26000 = 224000
- 注文5: 3500×10 + 12000×2 = 35000 + 24000 = 59000
- 合計: 283000（注文数: 2）
2024-09: 注文6,7
- 注文6: 65000×1 + 3500×3 = 65000 + 10500 = 75500
- 注文7: 89000×2 + 4800×5 = 178000 + 24000 = 202000
- 合計: 277500（注文数: 2）
2024-10: 注文8,9
- 注文8: 198000×1 + 45000×2 = 198000 + 90000 = 288000
- 注文9: 12000×1 + 6500×3 = 12000 + 19500 = 31500
- 合計: 319500（注文数: 2）
2024-11: 注文10
- 注文10: 89000×3 + 3500×8 = 267000 + 28000 = 295000
- 合計: 295000（注文数: 1）

前月比:

2024-08: 283000 / 593100 × 100 = 47.7%
2024-09: 277500 / 283000 × 100 = 98.1%（98.0565... → 98.1）
2024-10: 319500 / 277500 × 100 = 115.1%（115.135... → 115.1）
2024-11: 295000 / 319500 × 100 = 92.3%（92.332... → 92.3）

PostgreSQL との違い: DATE_FORMAT(o.order_date, '%Y-%m') の代わりに TO_CHAR(o.order_date, 'YYYY-MM') を使用します。

実務ポイント: 実際のレポートでは、注文がない月も0として表示することが多いです。その場合はカレンダーテーブル（日付のマスタテーブル）と LEFT JOIN します。

問題 2 ⭐⭐（応用）営業担当者別の成績表

ビジネス背景: 営業チームのマネージャーが、各担当者のパフォーマンスを比較したいと考えています。注文を担当した従業員ごとの成績を出してください。

要件:

担当者名、担当した顧客数（重複なし）、注文件数、売上合計、平均注文金額を表示
売上合計の降順で並べる
平均注文金額は整数で丸める

期待結果:

+------------+----------------+-------------+------------+-----------+
| name       | customer_count | order_count | total_sales| avg_order |
+------------+----------------+-------------+------------+-----------+
| 中村真理   |              3 |           3 |     721000 |    240333 |
| 田中太郎   |              3 |           4 |     619100 |    154775 |
| 鈴木一郎   |              3 |           3 |     428000 |    142667 |
+------------+----------------+-------------+------------+-----------+

模範解答

SELECT
    e.name,
    COUNT(DISTINCT o.customer_id) AS customer_count,
    COUNT(DISTINCT o.order_id) AS order_count,
    SUM(od.quantity * p.price) AS total_sales,
    ROUND(SUM(od.quantity * p.price) / COUNT(DISTINCT o.order_id)) AS avg_order
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY e.employee_id, e.name
ORDER BY total_sales DESC;

解説:

employees → orders → order_details → products の4テーブルを結合する
COUNT(DISTINCT o.customer_id) で担当した重複なしの顧客数を求める
COUNT(DISTINCT o.order_id) で注文件数を求める。order_details との結合で行が増えるため DISTINCT が必要
SUM(od.quantity * p.price) で売上合計を計算する
平均注文金額 = 売上合計 / 注文件数

検証:

orders テーブルの employee_id による割り当て:

田中太郎(employee_id=1): 注文1,3,6,9
- 顧客: 1,1,2,3 → DISTINCT 3社
- 注文1: 413500, 注文3: 98600, 注文6: 75500, 注文9: 31500
- 合計: 619100, 注文数: 4, 平均: 619100/4 = 154775
鈴木一郎(employee_id=3): 注文2,5,8
- 顧客: 2,4,1 → DISTINCT 3社
- 注文2: 81000, 注文5: 59000, 注文8: 288000
- 合計: 428000, 注文数: 3, 平均: 428000/3 = 142666.67 → ROUND → 142667
中村真理(employee_id=8): 注文4,7,10
- 顧客: 3,5,4 → DISTINCT 3社
- 注文4: 224000, 注文7: 202000, 注文10: 295000
- 合計: 721000, 注文数: 3, 平均: 721000/3 = 240333.33 → ROUND → 240333

ORDER BY total_sales DESC: 721000, 619100, 428000 ✓

実務ポイント: このようなレポートでは、「担当者にアサインされていない注文」や「注文を一度も受けていない営業担当者」の扱いを事前に決めておく必要があります。LEFT JOIN にすれば後者も含められます。

問題 3 ⭐⭐（応用）商品カテゴリ別の売上分析

ビジネス背景: 商品戦略を検討するにあたり、カテゴリごとの売上構成を把握する必要があります。各カテゴリの販売実績と売上構成比を出してください。

要件:

カテゴリ名、販売商品数（種類数）、販売個数合計、売上合計、売上構成比（%）を表示
売上構成比は小数第1位まで表示
売上合計の降順で並べる

期待結果:

+----------+---------------+-----------+------------+-----------+
| category | product_count | total_qty | total_sales| sales_pct |
+----------+---------------+-----------+------------+-----------+
| パソコン |             2 |        10 |    1326000 |      75.0 |
| 周辺機器 |             4 |        46 |     242100 |      13.7 |
| モニター |             2 |         4 |     200000 |      11.3 |
+----------+---------------+-----------+------------+-----------+

模範解答

SELECT
    p.category,
    COUNT(DISTINCT p.product_id) AS product_count,
    SUM(od.quantity) AS total_qty,
    SUM(od.quantity * p.price) AS total_sales,
    ROUND(
        SUM(od.quantity * p.price) / (
            SELECT SUM(od2.quantity * p2.price)
            FROM order_details od2
            JOIN products p2 ON od2.product_id = p2.product_id
        ) * 100,
        1
    ) AS sales_pct
FROM products p
JOIN order_details od ON p.product_id = od.product_id
GROUP BY p.category
ORDER BY total_sales DESC;

別解（ウィンドウ関数を使用）:

WITH category_sales AS (
    SELECT
        p.category,
        COUNT(DISTINCT p.product_id) AS product_count,
        SUM(od.quantity) AS total_qty,
        SUM(od.quantity * p.price) AS total_sales
    FROM products p
    JOIN order_details od ON p.product_id = od.product_id
    GROUP BY p.category
)
SELECT
    category,
    product_count,
    total_qty,
    total_sales,
    ROUND(total_sales / SUM(total_sales) OVER () * 100, 1) AS sales_pct
FROM category_sales
ORDER BY total_sales DESC;

解説:

products と order_details を結合し、カテゴリごとに集計する
売上構成比は、各カテゴリの売上をフル合計で割って算出する
別解のウィンドウ関数版では SUM(total_sales) OVER () でパーティションなしの合計（全カテゴリの総売上）を取得している

検証:

商品ごとの販売数量と売上:

パソコン:
- Product 1 (ノートPC Pro, 198000): 数量 2+1+1 = 4, 売上 792000
- Product 6 (ノートPC Light, 89000): 数量 1+2+3 = 6, 売上 534000
- 合計: 商品種類 2, 数量 10, 売上 1326000
周辺機器:
- Product 2 (ワイヤレスマウス, 3500): 数量 5+10+3+8 = 26, 売上 91000
- Product 3 (USBハブ, 4800): 数量 2+5 = 7, 売上 33600
- Product 5 (メカニカルキーボード, 12000): 数量 3+2+1 = 6, 売上 72000
- Product 7 (Webカメラ HD, 6500): 数量 4+3 = 7, 売上 45500
- 合計: 商品種類 4, 数量 46, 売上 242100
モニター:
- Product 4 (4Kモニター, 45000): 数量 1+2 = 3, 売上 135000
- Product 8 (ゲーミングモニター, 65000): 数量 1, 売上 65000
- 合計: 商品種類 2, 数量 4, 売上 200000

総売上: 1326000 + 242100 + 200000 = 1768100

構成比:

パソコン: 1326000 / 1768100 × 100 = 74.99... → 75.0%
周辺機器: 242100 / 1768100 × 100 = 13.69... → 13.7%
モニター: 200000 / 1768100 × 100 = 11.31... → 11.3%

合計: 75.0 + 13.7 + 11.3 = 100.0% ✓

実務ポイント: 構成比の合計が丸め誤差で100%にならない場合があります。レポートの精度要件に応じて、最大カテゴリで調整する（残差を加減する）手法が使われます。

問題 4 ⭐⭐⭐（チャレンジ）顧客のRFM分析的レポート

ビジネス背景: マーケティング部門がCRM施策を検討しています。各顧客の Recency（最終注文日）、Frequency（注文回数）、Monetary（累計金額）を算出し、ランク付けしてください。

要件:

顧客名、最終注文日、注文回数、累計金額を表示
累計金額でランク付け（1位から）し、上位を「優良」、それ以外を「一般」と分類
- 累計金額上位2名を「優良」、それ以外を「一般」とする
累計金額の降順で並べる

期待結果:

+----------------------------+------------+--------+-----------+------+--------+
| customer_name              | last_order | freq   | monetary  | rank | status |
+----------------------------+------------+--------+-----------+------+--------+
| 株式会社ABC                | 2024-10-01 |      3 |    800100 |    1 | 優良   |
| JKLテクノロジー            | 2024-11-01 |      2 |    354000 |    2 | 優良   |
| GHI商事                    | 2024-10-15 |      2 |    255500 |    3 | 一般   |
| MNOサービス                | 2024-09-10 |      1 |    202000 |    4 | 一般   |
| DEFコーポレーション        | 2024-09-01 |      2 |    156500 |    5 | 一般   |
+----------------------------+------------+--------+-----------+------+--------+

模範解答

WITH customer_rfm AS (
    SELECT
        c.customer_name,
        MAX(o.order_date) AS last_order,
        COUNT(DISTINCT o.order_id) AS freq,
        SUM(od.quantity * p.price) AS monetary
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY c.customer_id, c.customer_name
),
ranked AS (
    SELECT
        customer_name,
        last_order,
        freq,
        monetary,
        RANK() OVER (ORDER BY monetary DESC) AS `rank`
    FROM customer_rfm
)
SELECT
    customer_name,
    last_order,
    freq,
    monetary,
    `rank`,
    CASE WHEN `rank` <= 2 THEN '優良' ELSE '一般' END AS status
FROM ranked
ORDER BY monetary DESC;

解説:

CTE customer_rfm で各顧客の最終注文日・注文回数・累計金額を集計する
CTE ranked で累計金額の降順に RANK() でランクを付与する
最終SELECTで CASE 式を使い、ランク2位以内を「優良」、それ以外を「一般」と分類する

検証:

各顧客の注文と金額:

株式会社ABC(customer_id=1): 注文1,3,8
- 注文1: 413500, 注文3: 98600, 注文8: 288000
- last_order: 2024-10-01, freq: 3, monetary: 800100
DEFコーポレーション(customer_id=2): 注文2,6
- 注文2: 81000, 注文6: 75500
- last_order: 2024-09-01, freq: 2, monetary: 156500
GHI商事(customer_id=3): 注文4,9
- 注文4: 224000, 注文9: 31500
- last_order: 2024-10-15, freq: 2, monetary: 255500
JKLテクノロジー(customer_id=4): 注文5,10
- 注文5: 59000, 注文10: 295000
- last_order: 2024-11-01, freq: 2, monetary: 354000
MNOサービス(customer_id=5): 注文7
- 注文7: 202000
- last_order: 2024-09-10, freq: 1, monetary: 202000

降順: 800100, 354000, 255500, 202000, 156500 ✓

RFM分析とは: Recency（最新購買日）、Frequency（購買頻度）、Monetary（累計購買金額）の3軸で顧客をセグメント分けするマーケティング手法です。実務では各軸を5段階に分け、125のセグメントで分析することが一般的です。

実務ポイント: 本問では簡易的にMonetaryのみでランク付けしていますが、実際のRFM分析ではNTILE()ウィンドウ関数で各軸を5分位に分けることが多いです。

問題 5 ⭐⭐⭐（チャレンジ）部署別の人件費レポート

ビジネス背景: 人事部が部署間の給与バランスを分析しています。部署ごとの人件費状況と全社平均との比較を出してください。

要件:

部署名、人数、平均給与、最高給与者名、全社平均給与との差を表示
部署に所属していない従業員（department_id が NULL）は部署集計から除外する
全社平均は全従業員（department_id が NULL の従業員を含む）で計算する
平均給与は整数に丸める
部署IDの昇順で並べる

期待結果:

+-----------+----------+------------+--------------+----------+
| dept_name | emp_count| avg_salary | top_earner   | diff_avg |
+-----------+----------+------------+--------------+----------+
| 営業部    |        3 |     320000 | 田中太郎     |   -18000 |
| 開発部    |        3 |     383333 | 伊藤健太     |    45333 |
| 人事部    |        2 |     325000 | 高橋美咲     |   -13000 |
| 経理部    |        1 |     330000 | 山本大輔     |    -8000 |
+-----------+----------+------------+--------------+----------+

模範解答

WITH dept_stats AS (
    SELECT
        d.department_id,
        d.department_name AS dept_name,
        COUNT(*) AS emp_count,
        ROUND(AVG(e.salary)) AS avg_salary,
        MAX(e.salary) AS max_salary
    FROM departments d
    JOIN employees e ON d.department_id = e.department_id
    GROUP BY d.department_id, d.department_name
),
company_avg AS (
    SELECT ROUND(AVG(salary)) AS overall_avg
    FROM employees
),
top_earners AS (
    SELECT
        e.department_id,
        e.name AS top_earner
    FROM employees e
    JOIN (
        SELECT department_id, MAX(salary) AS max_salary
        FROM employees
        WHERE department_id IS NOT NULL
        GROUP BY department_id
    ) m ON e.department_id = m.department_id AND e.salary = m.max_salary
)
SELECT
    ds.dept_name,
    ds.emp_count,
    ds.avg_salary,
    te.top_earner,
    ds.avg_salary - ca.overall_avg AS diff_avg
FROM dept_stats ds
CROSS JOIN company_avg ca
JOIN top_earners te ON ds.department_id = te.department_id
ORDER BY ds.department_id;

解説:

CTE dept_stats で部署ごとの人数・平均給与・最高給与を集計する
CTE company_avg で全社の平均給与を求める（全従業員10名）
CTE top_earners で各部署の最高給与者名を特定する
3つのCTEを結合して最終結果を出す

検証:

全社平均: (350000+420000+300000+380000+450000+280000+330000+310000+270000+290000) / 10 = 3380000 / 10 = 338000

部署ごと:

営業部(1): 田中太郎(350000), 鈴木一郎(300000), 中村真理(310000) → 3人, 平均 960000/3 = 320000, 最高: 田中太郎(350000), 差: 320000-338000 = -18000
開発部(2): 佐藤花子(420000), 伊藤健太(450000), 渡辺さくら(280000) → 3人, 平均 1150000/3 = 383333.33 → 383333, 最高: 伊藤健太(450000), 差: 383333-338000 = 45333
人事部(3): 高橋美咲(380000), 小林誠(270000) → 2人, 平均 650000/2 = 325000, 最高: 高橋美咲(380000), 差: 325000-338000 = -13000
経理部(4): 山本大輔(330000) → 1人, 平均 330000, 最高: 山本大輔(330000), 差: 330000-338000 = -8000

マーケティング部(5): 所属従業員0人のため、JOINで除外される ✓
加藤優子(department_id=NULL): 部署集計から除外されるが、全社平均の計算には含まれる ✓

実務ポイント: 最高給与者が同額で複数いる場合、この解法では複数行が出ます。1人だけ表示したい場合は ROW_NUMBER() で順位を付け、rn = 1 に絞り込みます。

問題 6 ⭐⭐⭐（チャレンジ）在庫切れリスクのある商品

ビジネス背景: 在庫管理担当者が、現在の販売ペースで在庫が何ヶ月持つかを把握し、発注計画を立てたいと考えています。直近3ヶ月（2024年9月〜11月）の平均月間販売数をもとに、在庫の残り月数を算出してください。

要件:

商品名、現在庫数、直近3ヶ月の販売数合計、月平均販売数、推定残り月数を表示
直近3ヶ月 = 2024-09-01 以降の注文が対象
月平均販売数 = 直近3ヶ月の販売数合計 / 3（小数第2位まで）
推定残り月数 = 現在庫数 / 月平均販売数（小数第1位まで）
直近3ヶ月に販売のない商品も表示する（月平均0の場合は残り月数をNULLとする）
推定残り月数の昇順で並べる（NULLは末尾）

期待結果:

+----------------------------+-------+----------+-----------+--------------+
| product_name               | stock | sold_3mo | avg_monthly| months_left |
+----------------------------+-------+----------+-----------+--------------+
| 4Kモニター 27インチ        |    30 |        2 |      0.67 |         45.0 |
| ワイヤレスマウス           |   200 |       11 |      3.67 |         54.5 |
| ノートPC Light             |   100 |        5 |      1.67 |         60.0 |
| ゲーミングモニター         |    20 |        1 |      0.33 |         60.0 |
| USBハブ 7ポート            |   150 |        5 |      1.67 |         90.0 |
| Webカメラ HD               |   120 |        3 |      1.00 |        120.0 |
| ノートPC Pro               |    50 |        1 |      0.33 |        150.0 |
| メカニカルキーボード       |    80 |        1 |      0.33 |        240.0 |
+----------------------------+-------+----------+-----------+--------------+

模範解答

WITH recent_sales AS (
    SELECT
        od.product_id,
        SUM(od.quantity) AS sold_3mo
    FROM order_details od
    JOIN orders o ON od.order_id = o.order_id
    WHERE o.order_date >= '2024-09-01'
    GROUP BY od.product_id
)
SELECT
    p.product_name,
    p.stock,
    COALESCE(rs.sold_3mo, 0) AS sold_3mo,
    ROUND(COALESCE(rs.sold_3mo, 0) / 3, 2) AS avg_monthly,
    CASE
        WHEN COALESCE(rs.sold_3mo, 0) = 0 THEN NULL
        ELSE ROUND(p.stock / (rs.sold_3mo / 3), 1)
    END AS months_left
FROM products p
LEFT JOIN recent_sales rs ON p.product_id = rs.product_id
ORDER BY months_left ASC;

解説:

CTE recent_sales で2024-09-01以降の商品別販売数量を集計する
products テーブルと LEFT JOIN し、販売のない商品も含める
COALESCE で販売なしの場合を0に変換する
月平均 = 販売合計 / 3（3ヶ月分）
残り月数 = 現在庫 / 月平均販売数。販売なしの場合は NULL
ORDER BY months_left ASC で NULL は末尾に配置される（MySQL のデフォルト動作）

検証:

2024-09-01以降の注文: 注文6(9/1), 7(9/10), 8(10/1), 9(10/15), 10(11/1)

各商品の販売数（p.stock / (rs.sold_3mo / 3) = p.stock * 3 / rs.sold_3mo）:

Product 1 (ノートPC Pro): 注文8で1個 → sold=1, avg=0.33, months=50×3/1 = 150.0
Product 2 (ワイヤレスマウス): 注文6で3個 + 注文10で8個 → sold=11, avg=3.67, months=200×3/11 = 54.5
Product 3 (USBハブ): 注文7で5個 → sold=5, avg=1.67, months=150×3/5 = 90.0
Product 4 (4Kモニター): 注文8で2個 → sold=2, avg=0.67, months=30×3/2 = 45.0
Product 5 (メカニカルキーボード): 注文9で1個 → sold=1, avg=0.33, months=80×3/1 = 240.0
Product 6 (ノートPC Light): 注文7で2個 + 注文10で3個 → sold=5, avg=1.67, months=100×3/5 = 60.0
Product 7 (Webカメラ HD): 注文9で3個 → sold=3, avg=1.00, months=120×3/3 = 120.0
Product 8 (ゲーミングモニター): 注文6で1個 → sold=1, avg=0.33, months=20×3/1 = 60.0

months_left 昇順: 45.0, 54.5, 60.0, 60.0, 90.0, 120.0, 150.0, 240.0 ✓

PostgreSQL との違い: MySQL では ORDER BY months_left ASC で NULL が末尾に来ます。PostgreSQL でも同じデフォルト動作ですが、NULLS FIRST / NULLS LAST で明示的に指定できます。

実務ポイント: 実際の在庫管理では、季節変動やトレンドを考慮した需要予測が必要です。また、リードタイム（発注から納品までの日数）を加味して、「残り月数 < リードタイム」の商品を自動的にアラートする仕組みを構築することが一般的です。

問題 7 ⭐⭐⭐（チャレンジ）注文の増減トレンド

ビジネス背景: 事業の成長率を把握するため、月ごとの注文数と売上の前月比増減率を確認したいと考えています。

要件:

年月、注文数、前月の注文数、注文数の増減率（%）を表示
増減率は小数第1位まで表示
増減率 = (当月 - 前月) / 前月 × 100

期待結果:

+---------+-------------+------------+---------------+
| 年月    | order_count | prev_count | change_rate   |
+---------+-------------+------------+---------------+
| 2024-07 |           3 |       NULL |          NULL |
| 2024-08 |           2 |          3 |         -33.3 |
| 2024-09 |           2 |          2 |           0.0 |
| 2024-10 |           2 |          2 |           0.0 |
| 2024-11 |           1 |          2 |         -50.0 |
+---------+-------------+------------+---------------+

模範解答

WITH monthly_orders AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS 年月,
        COUNT(*) AS order_count
    FROM orders
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    年月,
    order_count,
    LAG(order_count) OVER (ORDER BY 年月) AS prev_count,
    ROUND(
        (order_count - LAG(order_count) OVER (ORDER BY 年月))
        / LAG(order_count) OVER (ORDER BY 年月) * 100,
        1
    ) AS change_rate
FROM monthly_orders
ORDER BY 年月;

解説:

CTE monthly_orders で月ごとの注文数を集計する
LAG() で前月の注文数を取得する
増減率 = (当月 - 前月) / 前月 × 100

検証:

月別注文数:

2024-07: 注文1,2,3 → 3件
2024-08: 注文4,5 → 2件
2024-09: 注文6,7 → 2件
2024-10: 注文8,9 → 2件
2024-11: 注文10 → 1件

増減率:

2024-08: (2-3)/3 × 100 = -33.333... → -33.3
2024-09: (2-2)/2 × 100 = 0.0
2024-10: (2-2)/2 × 100 = 0.0
2024-11: (1-2)/2 × 100 = -50.0

実務ポイント: 注文数だけでなく売上金額の増減率も同時に見ると、「注文数は減ったが客単価が上がった」などの洞察が得られます。問題1のクエリと組み合わせて使うと効果的です。

問題 8 ⭐⭐⭐（チャレンジ）商品のクロスセル分析

ビジネス背景: EC サイトの「この商品を買った人はこんな商品も買っています」レコメンド機能のために、同一注文内で一緒に購入されている商品ペアを分析してください。

要件:

同一注文内で一緒に購入された商品ペア（商品A, 商品B）とその出現回数を表示
商品A の product_id < 商品B の product_id（重複ペアを排除）
出現回数の降順、同数の場合は product_id の昇順で並べる

期待結果:

+----------------------------+----------------------------+----------+
| product_a                  | product_b                  | co_count |
+----------------------------+----------------------------+----------+
| USBハブ 7ポート            | ノートPC Light             |        2 |
| ノートPC Pro               | ワイヤレスマウス           |        1 |
| ノートPC Pro               | Webカメラ HD               |        1 |
| ノートPC Pro               | 4Kモニター 27インチ        |        1 |
| ワイヤレスマウス           | ノートPC Light             |        1 |
| ワイヤレスマウス           | メカニカルキーボード       |        1 |
| ワイヤレスマウス           | ゲーミングモニター         |        1 |
| 4Kモニター 27インチ        | メカニカルキーボード       |        1 |
| メカニカルキーボード       | Webカメラ HD               |        1 |
+----------------------------+----------------------------+----------+

模範解答

SELECT
    p1.product_name AS product_a,
    p2.product_name AS product_b,
    COUNT(*) AS co_count
FROM order_details od1
JOIN order_details od2
    ON od1.order_id = od2.order_id
    AND od1.product_id < od2.product_id
JOIN products p1 ON od1.product_id = p1.product_id
JOIN products p2 ON od2.product_id = p2.product_id
GROUP BY od1.product_id, od2.product_id, p1.product_name, p2.product_name
ORDER BY co_count DESC, od1.product_id ASC, od2.product_id ASC;

解説:

order_details を自己結合し、同じ注文内の異なる商品ペアを生成する
od1.product_id < od2.product_id で (A,B) と (B,A) の重複を排除する
products テーブルを2回結合して商品名を取得する
ペアごとに COUNT(*) で出現回数を集計する

検証:

各注文の商品ペア（product_id の昇順ペア）:

注文1: products 1,2 → (1,2)
注文2: products 4,5 → (4,5)
注文3: products 3,6 → (3,6)
注文4: products 1,7 → (1,7)
注文5: products 2,5 → (2,5)
注文6: products 2,8 → (2,8)
注文7: products 3,6 → (3,6)
注文8: products 1,4 → (1,4)
注文9: products 5,7 → (5,7)
注文10: products 2,6 → (2,6)

ペアの出現回数:

(3,6) USBハブ + ノートPC Light: 2回
(1,2) ノートPC Pro + ワイヤレスマウス: 1回
(1,4) ノートPC Pro + 4Kモニター: 1回
(1,7) ノートPC Pro + Webカメラ: 1回
(2,5) ワイヤレスマウス + メカニカルキーボード: 1回
(2,6) ワイヤレスマウス + ノートPC Light: 1回
(2,8) ワイヤレスマウス + ゲーミングモニター: 1回
(4,5) 4Kモニター + メカニカルキーボード: 1回
(5,7) メカニカルキーボード + Webカメラ: 1回

co_count DESC → product_id ASC の順: (3,6), (1,2), (1,4), (1,7), (2,5), (2,6), (2,8), (4,5), (5,7) ✓

実務ポイント: 大規模なECサイトでは自己結合のコストが高いため、バッチ処理で事前に集計テーブルを作成しておくことが一般的です。また、商品ペアだけでなくリフト値（期待値に対する実際の共起率の比率）を算出すると、より意味のあるレコメンドが可能になります。

問題 9 ⭐⭐⭐（チャレンジ）新規顧客 vs 既存顧客の売上比較

ビジネス背景: マーケティング施策の効果を測るため、各月の売上が新規顧客（その月が初回注文）と既存顧客（過去に注文履歴あり）のどちらから生まれているかを分析してください。

要件:

月ごとに、新規顧客の売上合計と既存顧客の売上合計を表示
顧客の「初回注文月」＝その顧客の最初の order_date が属する月
ある注文が属する月と、その顧客の初回注文月が一致すれば「新規」、そうでなければ「既存」

期待結果:

+---------+-----------+-----------+
| 年月    | new_sales | rep_sales |
+---------+-----------+-----------+
| 2024-07 |    593100 |         0 |
| 2024-08 |    283000 |         0 |
| 2024-09 |    202000 |     75500 |
| 2024-10 |         0 |    319500 |
| 2024-11 |         0 |    295000 |
+---------+-----------+-----------+

模範解答

WITH customer_first_month AS (
    SELECT
        customer_id,
        DATE_FORMAT(MIN(order_date), '%Y-%m') AS first_month
    FROM orders
    GROUP BY customer_id
),
order_sales AS (
    SELECT
        o.order_id,
        o.customer_id,
        DATE_FORMAT(o.order_date, '%Y-%m') AS order_month,
        SUM(od.quantity * p.price) AS order_total
    FROM orders o
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY o.order_id, o.customer_id, DATE_FORMAT(o.order_date, '%Y-%m')
)
SELECT
    os.order_month AS 年月,
    SUM(CASE WHEN os.order_month = cfm.first_month THEN os.order_total ELSE 0 END) AS new_sales,
    SUM(CASE WHEN os.order_month != cfm.first_month THEN os.order_total ELSE 0 END) AS rep_sales
FROM order_sales os
JOIN customer_first_month cfm ON os.customer_id = cfm.customer_id
GROUP BY os.order_month
ORDER BY os.order_month;

解説:

CTE customer_first_month で各顧客の初回注文月を求める
CTE order_sales で注文ごとの売上金額を集計する
各注文が属する月と顧客の初回注文月を比較し、一致すれば新規、異なれば既存として CASE で振り分ける
月ごとに SUM で集計する

検証:

各顧客の初回注文月:

Customer 1 (株式会社ABC): MIN(2024-07-01, 2024-07-10, 2024-10-01) → 2024-07
Customer 2 (DEFコーポレーション): MIN(2024-07-05, 2024-09-01) → 2024-07
Customer 3 (GHI商事): MIN(2024-08-01, 2024-10-15) → 2024-08
Customer 4 (JKLテクノロジー): MIN(2024-08-15, 2024-11-01) → 2024-08
Customer 5 (MNOサービス): MIN(2024-09-10) → 2024-09

各注文の分類:

注文1 (customer 1, 2024-07, 413500): first=2024-07 → 新規 ✓
注文2 (customer 2, 2024-07, 81000): first=2024-07 → 新規 ✓
注文3 (customer 1, 2024-07, 98600): first=2024-07 → 新規 ✓
注文4 (customer 3, 2024-08, 224000): first=2024-08 → 新規 ✓
注文5 (customer 4, 2024-08, 59000): first=2024-08 → 新規 ✓
注文6 (customer 2, 2024-09, 75500): first=2024-07 → 既存 ✓
注文7 (customer 5, 2024-09, 202000): first=2024-09 → 新規 ✓
注文8 (customer 1, 2024-10, 288000): first=2024-07 → 既存 ✓
注文9 (customer 3, 2024-10, 31500): first=2024-08 → 既存 ✓
注文10 (customer 4, 2024-11, 295000): first=2024-08 → 既存 ✓

月別集計:

2024-07: 新規 413500+81000+98600 = 593100, 既存 0
2024-08: 新規 224000+59000 = 283000, 既存 0
2024-09: 新規 202000, 既存 75500
2024-10: 新規 0, 既存 288000+31500 = 319500
2024-11: 新規 0, 既存 295000 ✓

実務ポイント: 新規顧客からの売上比率が高い場合は、マーケティング施策（広告投資）が効いている一方でリピート率に課題がある可能性があります。逆に既存顧客比率が高い場合は、安定した売上基盤がある一方で成長が鈍化している可能性があります。両方のバランスを見ることが重要です。

問題 10 ⭐⭐⭐（チャレンジ）総合ダッシュボード用クエリ

ビジネス背景: 社内ダッシュボードに表示するために、以下の4つの情報を 1つのクエリセット で取得してください。実務では複数のクエリを1回のリクエストで実行し、画面の各パネルに表示します。

要件:

売上サマリー: 総売上金額、総注文数、総顧客数、平均注文金額
売上トップ3商品: 売上金額上位3商品の商品名と売上金額
売上トップ3顧客: 累計金額上位3顧客の顧客名と累計金額
直近の注文5件: 注文日の新しい順に5件分の注文ID、顧客名、注文金額

期待結果:

パネル1: 売上サマリー

+-------------+-------------+----------------+-----------+
| total_sales | total_orders| total_customers| avg_order |
+-------------+-------------+----------------+-----------+
|     1768100 |          10 |              5 |    176810 |
+-------------+-------------+----------------+-----------+

パネル2: 売上トップ3商品

+----------------------------+-------------+
| product_name               | product_sales|
+----------------------------+-------------+
| ノートPC Pro               |      792000 |
| ノートPC Light             |      534000 |
| 4Kモニター 27インチ        |      135000 |
+----------------------------+-------------+

パネル3: 売上トップ3顧客

+----------------------------+-----------+
| customer_name              | monetary  |
+----------------------------+-----------+
| 株式会社ABC                |    800100 |
| JKLテクノロジー            |    354000 |
| GHI商事                    |    255500 |
+----------------------------+-----------+

パネル4: 直近の注文5件

+----------+----------------------------+------------+-------------+
| order_id | customer_name              | order_date | order_total |
+----------+----------------------------+------------+-------------+
|       10 | JKLテクノロジー            | 2024-11-01 |      295000 |
|        9 | GHI商事                    | 2024-10-15 |       31500 |
|        8 | 株式会社ABC                | 2024-10-01 |      288000 |
|        7 | MNOサービス                | 2024-09-10 |      202000 |
|        6 | DEFコーポレーション        | 2024-09-01 |       75500 |
+----------+----------------------------+------------+-------------+

模範解答

-- パネル1: 売上サマリー
SELECT
    SUM(od.quantity * p.price) AS total_sales,
    COUNT(DISTINCT o.order_id) AS total_orders,
    COUNT(DISTINCT o.customer_id) AS total_customers,
    ROUND(SUM(od.quantity * p.price) / COUNT(DISTINCT o.order_id)) AS avg_order
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id;

-- パネル2: 売上トップ3商品
SELECT
    p.product_name,
    SUM(od.quantity * p.price) AS product_sales
FROM order_details od
JOIN products p ON od.product_id = p.product_id
GROUP BY p.product_id, p.product_name
ORDER BY product_sales DESC
LIMIT 3;

-- パネル3: 売上トップ3顧客
SELECT
    c.customer_name,
    SUM(od.quantity * p.price) AS monetary
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
ORDER BY monetary DESC
LIMIT 3;

-- パネル4: 直近の注文5件
SELECT
    o.order_id,
    c.customer_name,
    o.order_date,
    SUM(od.quantity * p.price) AS order_total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY o.order_id, c.customer_name, o.order_date
ORDER BY o.order_date DESC
LIMIT 5;

別解（UNION ALL で1つのクエリにまとめる方法）:

SELECT 'summary' AS panel, NULL AS rank_no,
       CAST(SUM(od.quantity * p.price) AS CHAR) AS col1,
       CAST(COUNT(DISTINCT o.order_id) AS CHAR) AS col2,
       CAST(COUNT(DISTINCT o.customer_id) AS CHAR) AS col3,
       CAST(ROUND(SUM(od.quantity * p.price) / COUNT(DISTINCT o.order_id)) AS CHAR) AS col4
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id

UNION ALL

SELECT 'top_products', CAST(rn AS CHAR), product_name, CAST(product_sales AS CHAR), NULL, NULL
FROM (
    SELECT
        p.product_name,
        SUM(od.quantity * p.price) AS product_sales,
        ROW_NUMBER() OVER (ORDER BY SUM(od.quantity * p.price) DESC) AS rn
    FROM order_details od
    JOIN products p ON od.product_id = p.product_id
    GROUP BY p.product_id, p.product_name
) ranked
WHERE rn <= 3

UNION ALL

SELECT 'top_customers', CAST(rn AS CHAR), customer_name, CAST(monetary AS CHAR), NULL, NULL
FROM (
    SELECT
        c.customer_name,
        SUM(od.quantity * p.price) AS monetary,
        ROW_NUMBER() OVER (ORDER BY SUM(od.quantity * p.price) DESC) AS rn
    FROM customers c
    JOIN orders o ON c.customer_id = o.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY c.customer_id, c.customer_name
) ranked
WHERE rn <= 3

UNION ALL

SELECT 'recent_orders', CAST(rn AS CHAR),
       CONCAT(order_id, ':', customer_name),
       CAST(order_date AS CHAR), CAST(order_total AS CHAR), NULL
FROM (
    SELECT
        o.order_id,
        c.customer_name,
        o.order_date,
        SUM(od.quantity * p.price) AS order_total,
        ROW_NUMBER() OVER (ORDER BY o.order_date DESC) AS rn
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_details od ON o.order_id = od.order_id
    JOIN products p ON od.product_id = p.product_id
    GROUP BY o.order_id, c.customer_name, o.order_date
) ranked
WHERE rn <= 5;

解説:

通常は 4つの独立したクエリ をアプリケーション側から一度に発行します（MySQLの複数ステートメント実行）。これが最もシンプルで保守しやすい方法です。

UNION ALL でまとめる方法は、全結果を1回のクエリで取得したい場合に使えますが、カラムの型を合わせる必要があり可読性が下がります。実務では用途に応じて選択してください。

検証:

パネル1:

全注文の売上合計: 593100 + 283000 + 277500 + 319500 + 295000 = 1768100
総注文数: 10件, 総顧客数: 5社
平均注文金額: 1768100 / 10 = 176810

パネル2（商品別売上）:

Product 1 (ノートPC Pro): 198000×(2+1+1) = 792000
Product 6 (ノートPC Light): 89000×(1+2+3) = 534000
Product 4 (4Kモニター): 45000×(1+2) = 135000
Product 2 (ワイヤレスマウス): 3500×(5+10+3+8) = 91000
Product 5 (メカニカルキーボード): 12000×(3+2+1) = 72000
Product 8 (ゲーミングモニター): 65000×1 = 65000
Product 7 (Webカメラ): 6500×(4+3) = 45500
Product 3 (USBハブ): 4800×(2+5) = 33600
トップ3: ノートPC Pro(792000), ノートPC Light(534000), 4Kモニター(135000) ✓

パネル3（顧客別累計）:

Customer 1: 413500+98600+288000 = 800100
Customer 4: 59000+295000 = 354000
Customer 3: 224000+31500 = 255500
トップ3: 株式会社ABC(800100), JKLテクノロジー(354000), GHI商事(255500) ✓

パネル4（直近5件）:

注文10 (2024-11-01, JKLテクノロジー): 267000+28000 = 295000
注文9 (2024-10-15, GHI商事): 12000+19500 = 31500
注文8 (2024-10-01, 株式会社ABC): 198000+90000 = 288000
注文7 (2024-09-10, MNOサービス): 178000+24000 = 202000
注文6 (2024-09-01, DEFコーポレーション): 65000+10500 = 75500 ✓

実務ポイント: ダッシュボード用のクエリは頻繁に実行されるため、パフォーマンスが重要です。大規模データでは以下の対策が有効です:

マテリアライズドビュー（PostgreSQL）やサマリーテーブルを定期更新する

必要なインデックスを事前に設計する（第9回を参照）

キャッシュ機構をアプリケーション層で実装する

おわりに

全10回にわたる SQL 実践ドリルシリーズ、お疲れさまでした。

このシリーズで扱った内容を振り返ります:

回	テーマ	主な学習内容
第1回	SELECT の基本	SELECT, WHERE, ORDER BY, LIMIT
第2回	集約関数と GROUP BY	COUNT, SUM, AVG, GROUP BY, HAVING
第3回	JOIN	INNER JOIN, LEFT JOIN, 複数テーブル結合
第4回	サブクエリ	スカラー, 行, テーブルサブクエリ, EXISTS
第5回	INSERT / UPDATE / DELETE	データ操作言語 (DML)
第6回	テーブル設計	CREATE TABLE, 正規化, 制約
第7回	ウィンドウ関数	ROW_NUMBER, RANK, LAG, SUM OVER
第8回	CTE と再帰	WITH, 再帰CTE, 階層データ
第9回	パフォーマンス	INDEX, EXPLAIN, クエリ最適化
第10回	総合演習	実務想定の複合問題

SQL は書けば書くほど上達します。業務で新しいデータを扱うときは、まず SELECT * で中身を見て、JOIN で関連テーブルを繋ぎ、GROUP BY で集計する――この流れを繰り返してください。

参考

@kotaro_ai_lab
AI活用や開発効率化について発信しています。フォローお気軽にどうぞ！

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

【SQL実践ドリル 第10回】総合演習 ― 実務想定の複合問題10問

はじめに

問題 1 ⭐⭐（応用）月別の売上レポート

問題 2 ⭐⭐（応用）営業担当者別の成績表

問題 3 ⭐⭐（応用）商品カテゴリ別の売上分析

問題 4 ⭐⭐⭐（チャレンジ）顧客のRFM分析的レポート

問題 5 ⭐⭐⭐（チャレンジ）部署別の人件費レポート

問題 6 ⭐⭐⭐（チャレンジ）在庫切れリスクのある商品

問題 7 ⭐⭐⭐（チャレンジ）注文の増減トレンド

問題 8 ⭐⭐⭐（チャレンジ）商品のクロスセル分析

問題 9 ⭐⭐⭐（チャレンジ）新規顧客 vs 既存顧客の売上比較

問題 10 ⭐⭐⭐（チャレンジ）総合ダッシュボード用クエリ

おわりに

参考

【SQL実践ドリル第10回】総合演習 ― 実務想定の複合問題10問