プロンプトと適用可能なコンテキスト
PostgreSQLを使用して、2026年第2四半期（Q2）におけるカテゴリごとの売上上位3段階（異なる売上値のTop 3）の商品を、3位タイの商品をすべて含めて取得してください。データ契約を提示し、SQLを記述した上で、ROW_NUMBER、RANK、およびDENSE_RANKで結果が異なる理由を説明してください。
スキーマは以下の通りです：
orders( order_id bigint primary key, ordered_at timestamptz, status text )
order_items( order_id bigint, category_id bigint, product_id bigint, quantity integer, unit_price numeric(12, 2) )
合意されたデータ契約は次の通りです：COMPLETED の注文のみを対象とする；四半期はUTCの半開区間 [2026-04-01, 2026-07-01) として定義する；商品の売上は対象明細行の quantity * unit_price の合計とする；返金は提示されたモデルの対象外とする；「上位3位」はカテゴリごとの上位3つの異なる売上レベル（distinct revenue levels）を意味し、第3レベルでタイ（同率）となったすべての商品を含める。対象となる売上がない商品は出力されず、3レベル未満のカテゴリはそのすべてのレベルを返す。
この中難度の問題は、分析的SQLを必要とするデータアナリスト、データエンジニア、バックエンド開発者の職種に適しています。2026年時点の公開SQL面接教材でも、グループごとのTop-N取得、ウィンドウ関数、タイの挙動は定番の演習として扱われています。特定の企業への帰属は検証できないため、代表的なSQL面接課題として扱います。
面接官が評価するポイント
第一のシグナルは、出力の粒度（grain）を明確に定義しているかです。order_items の1行は注文明細ですが、目標とする出力はカテゴリと商品のペアごとに1行です。生の明細行をランク付けすると、1つの商品が複数の順位を占めてしまいます。構文上正しくても、ビジネス要件に合わないクエリになるリスクがあります。
第二のシグナルは、「上位3位」を明示的なタイセマンティクスに変換できるかです。ROW_NUMBER、RANK、DENSE_RANK はすべてカテゴリ内で順序付けできますが、回答する問いが異なります。優秀な候補者はまず、必要な結果が厳密に3行なのか、オリンピック方式（競技ランク）なのか、それとも3つの異なる売上レベルなのかを確認します。
第三のシグナルは、SQLの論理的評価順序の理解です。ウィンドウ関数は WHERE、GROUP BY、HAVING、および通常の集約処理の後に実行されます。まず商品の売上を集計し、次のレベルでランク付けし、外部クエリでランクをフィルタリングする必要があります。PostgreSQLでは、同じクエリレベルの WHERE 句でウィンドウ関数のエイリアスを使用することはできません。
最後のシグナルは、検証可能性と保守性です。優れた回答では、時間境界、対象ステータス、通貨型、決定論的な表示順序、およびテストケースが定義されます。大規模環境では、「インデックスを追加する」という根拠のない解決策を提示するのではなく、まずランク付けステージに入る行数を減らすアプローチをとります。
回答前に確認すべき質問
- 「上位3位」は3行、競技順位の3位以内、それとも3つの異なる値のどれを意味しますか？ 3行なら
ROW_NUMBER、競技ランキングならRANK、そして今回の要件（タイを保持した3つの異なる売上レベル）にはDENSE_RANKを使用します。 - どのイベントとタイムゾーンで売上を期間に帰属させますか？ 注文、支払い、完了、返金のどの時点を基準にするかによってレポートは異なります。本問ではUTCの
orders.ordered_atと重複のない半開区間を使用します。 - どのステータスが対象ですか？ キャンセル、テスト、失敗した注文を含めると売上が水増しされます。本契約では
COMPLETEDのみを対象とします。 - 返金、割引、税金、通貨はどのように扱われますか？ 提供されたモデルには数量と取引単価のみが含まれています。返金テーブルや複数通貨が追加される場合は、ランク付けの前に純売上を計算するか、単一通貨に換算します。
- 1つの商品が複数のカテゴリに属することはありますか？ このクエリでは明細行の
(category_id, product_id)をファクトキーとして扱います。現在の製品ディメンションを結合すると過去のカテゴリ帰属が上書きされる可能性があるため、履歴が重要な場合は注文時のスナップショットまたは有効期間付き（effective-dated）ディメンションを使用します。 - 売上ゼロの商品は表示すべきですか？ ここでは表示しません。必要な場合は、集計結果を完全なカテゴリ・商品セットに外部結合（LEFT JOIN）し、ゼロが上位3レベルに含まれるかどうかを定義します。
- 出力順序は決定論的である必要がありますか？ 売上のみでランク付けします。ランキングウィンドウに
product_idを追加するとタイが解消されてしまいます。表示の安定化はORDER BY category_id, revenue_rank, product_idで個別に行います。
30秒で答える回答フレームワーク
「UTCのQ2で完了した注文をカテゴリおよび商品ごとに1行に集約し、要件がタイを含む3つの異なるレベルであるため、カテゴリ別・売上降順で DENSE_RANK を適用します。CTEでランクを計算し、外部クエリで revenue_rank <= 3 をフィルタリングし、最後のORDER BYで表示順を安定させます。タイ、四半期の境界値、キャンセル注文、および3レベル未満のカテゴリでテストを行います。」
完全な回答には、なぜ集計がランク付けに先行するのか、3つのランキング関数がどのように異なるのか、そしてレポート契約と実行プランの双方を大規模環境で検証する方法も説明に含める必要があります。
ステップ別の詳細解説
ステップ1：ビジネス要件を粒度と不変条件（Invariants）として定義する
結果のキーは order_id ではなく (category_id, product_id) です。集計ステージで各キーは1度だけ現れる必要があり、revenue はそのキーに該当する対象明細行の合計と一致しなければなりません。ランク付けは粒度を変更することなく、カテゴリ内のローカルなレベルを付与します。
構文を書く前に3つの不変条件を設定します：対象となる各明細行は正確に1回だけ寄与すること、異なる注文にまたがる同一商品は統合されること、カテゴリの revenue_rank はそのカテゴリ内の売上のみに依存すること。これらの不変条件により、重複した結合、明細行単位のランク付け、グローバルなランク付けの誤りを素早く特定できます。
ステップ2：商品粒度に集計する前にファクトをフィルタリングする
開始日には >= を、翌四半期の開始日には < を使用します。BETWEEN や 2026-06-30 23:59:59 とは異なり、半開区間を使用することで高精度のタイムスタンプを取りこぼさず、次の四半期とも綺麗に連続します。timestamptz リテラルに明示的な +00 を指定することで、データベースセッションのタイムゾーン設定から独立させます。
結合後、ステータスと期間でフィルタリングし、category_id, product_id でグループ化します。このPostgreSQLモデルでは、quantity * unit_price は正確な数値式のままであり、SUM は厳密な金額計算を維持します。利便性のために浮動小数点型にキャストしてはいけません。本番環境の売上レポートには通貨や返金のセマンティクスも必要ですが、提供されたカラムからこれらを作成することはできません。
ステップ3：タイ契約に基づいてランキング関数を選択する
あるカテゴリに商品売上 100, 100, 90, 80 があるとします：
ROW_NUMBERは1, 2, 3, 4を生成します。追加のキーがない場合、2つの100の行の相対順序は不定になります。この例では100の行が両方と90の行が1つ返されますが、3行目の境界をまたぐタイは任意に切り捨てられます。これは3行を保証するものであり、完全なタイを保証しません。RANKは1, 1, 3, 4を生成します。3以下でフィルタリングすると売上100と90が返され、異なるレベルは2つだけになります。DENSE_RANKは1, 1, 2, 3を生成します。3以下でフィルタリングすると4商品すべてが返され、正確に3つの異なるレベルになります。
したがって、本プロンプトには DENSE_RANK が必要です。そのウィンドウの ORDER BY には revenue DESC のみを含める必要があります。product_id を追加すると同額の行が同率（peer）ではなくなり、「タイを保持する」要件が意図しない順序付けにすり替わってしまいます。
ステップ4：2つのCTEで集計・ランク付け・フィルタリングを分離する
完全なPostgreSQLクエリは以下の通りです：
WITH product_revenue AS ( SELECT oi.category_id, oi.product_id, SUM(oi.quantity * oi.unit_price) AS revenue FROM orders AS o JOIN order_items AS oi ON oi.orderid = o.orderid WHERE o.status = 'COMPLETED' AND o.ordered_at >= TIMESTAMPTZ '2026-04-01 00:00:00+00' AND o.ordered_at < TIMESTAMPTZ '2026-07-01 00:00:00+00' GROUP BY oi.categoryid, oi.productid ), ranked AS ( SELECT category_id, product_id, revenue, DENSE_RANK() OVER ( PARTITION BY category_id ORDER BY revenue DESC ) AS revenue_rank FROM product_revenue ) SELECT categoryid, productid, revenue, revenue_rank FROM ranked WHERE revenue_rank <= 3 ORDER BY categoryid, revenuerank, product_id;
第1ステージで多数の注文明細を商品ファクトに集約します。第2ステージで各カテゴリ内の商品セットのみをソートします。これにより、外部クエリがウィンドウ関数の結果を参照してフィルタリングできます。各ステージはメトリクス契約、ランキング契約、出力契約に対応しており、それぞれの粒度と行数を個別に検証できます。
ステップ5：構文だけでなく正しさを論理的に証明する
どのカテゴリにおいても、第1ステージで商品ごとに1つの合計が作成されます。PARTITION BY category_id により他のカテゴリが比較対象から除外され、ORDER BY revenue DESC により同一の合計値が1つの同率グループとして定義されます。DENSE_RANK はギャップなしで同率グループに連番を振るため、<= 3 は上位3つの異なる合計値と、それに属するすべての商品を過不足なく選択します。
最後の ORDER BY はランクには影響せず、返される行を並べ替えるだけです。この区別は重要です。ウィンドウ関数内の順序付けはビジネス上のランクを定義し、最後の順序付けは再現可能な表示を定義します。これらには異なるキーを使用できます。
ステップ6：パフォーマンスの主張を客観的証拠に基づかせる
R 件の対象明細行が G 件のカテゴリ・商品グループになる場合、論理的な作業としては R 行をスキャンして集計し、その後カテゴリ内で G 行をソートします。ソート処理はカテゴリパーティションごとの G_c log G_c の総和として表現できますが、PostgreSQLはハッシュ集約、ソート集約、並列実行、ディスク退避（spill）のいずれかを選択する可能性があります。この計算量は物理実行計画を保証するものではありません。
本番規模のレプリカで EXPLAIN (ANALYZE, BUFFERS) を実行し、フィルタ後の行数、結合戦略、集計行数、ソートメモリ、一時ファイルを確認します。ステータスの選択性、分布、パーティショニングに応じて、orders(status, ordered_at, order_id) や order_items(order_id) がフィルタリングや結合に有効な場合があります。時間パーティショニングは大規模ファクトテーブルのプルーニングに効果的です。頻繁に実行されるレポートの場合は、日次で整合性を取ったカテゴリ・商品集約テーブルの保持を検討します。事前集計は鮮度、バックフィル、修正の複雑さと引き換えにクエリ速度を向上させます。「マテリアライズドビューを作成する」だけでは完全な設計とは言えません。
ステップ7：小規模データでセマンティクスを、大規模データでコストを検証する
最小限の正確性検証用テストデータとして、売上 100, 100, 90, 80, 70 を持つカテゴリ10を用意し、ランク 1, 1, 2, 3 の最初の4商品が返されることを確認します。カテゴリ20には 50, 40 のみを含め、両方が返されることを確認します。集計に含まれてはならないキャンセル注文1件と、除外されるべきちょうど 2026-07-01 00:00:00+00 の注文を1件含めます。
同一商品に対する複数の注文や明細行を追加して1つの合計にまとまることを確認し、異なる商品IDに同じ合計値を持たせて、ランクは同じでありながら最終出力順が安定することを確認します。大規模検証では、スキャンされたパーティション、集計カーディナリティ、ソート時のディスク退避、経過時間、ピークリソースをチェックします。出力の正確性と許容可能なコストは、別個に証明されるべき証拠です。
高品質な模範回答
「まずタイの挙動、時間の帰属基準、対象ステータスを確認します。要件はカテゴリごとの上位3つの異なる売上レベルと第3レベルのすべてのタイを含むことなので、DENSE_RANK を使用します。ROW_NUMBER は厳密に3行が必要な場合にのみ使用します。
出力粒度はカテゴリと商品ごとに1行ですが、ソース粒度は注文明細です。そのため、最初のCTEで注文と明細を結合し、UTC Q2の COMPLETED の注文を抽出し、category_id, product_id ごとに SUM(quantity * unit_price) を集計します。隣接する四半期と重複しないよう、4月1日を含み（inclusive）、7月1日を含まない（exclusive）半開区間を使用します。
2番目のCTEで DENSE_RANK() OVER (PARTITION BY category_id ORDER BY revenue DESC) を適用します。同額のピアが分割されてしまうのを防ぐため、このウィンドウに product_id は追加しません。PostgreSQLは同一クエリレベルの WHERE でウィンドウ結果をフィルタリングできないため、外部クエリで revenue_rank <= 3 を適用し、表示の安定化のためにカテゴリ、ランク、商品IDでソートします。
売上 100, 100, 90, 80 はランク 1, 1, 2, 3 となり、4商品すべてが返されます。キャンセル注文、四半期の右境界値、同一商品の複数明細、および3レベル未満のカテゴリでもテストを行います。大規模データでは、実行プランを参照してパーティションプルーニング、インデックス、スピルチューニング、事前集計を正当化する前に、フィルタリングと集約によって元の R 行がどれだけ削減されるかを測定します。」
この回答は、構文の前にビジネスセマンティクスを確立し、クエリ構造、正確性の証拠、測定に基づいたパフォーマンス改善アプローチを提示しています。単なる関数名や未検証のインデックス提案で済ませていません。
よくある間違い
- 注文明細を直接ランク付けする → 1つの商品が複数の順位を占め、出力粒度が誤る → ランク付けの前にカテゴリと商品で集計する。
- グローバルな
ORDER BY ... LIMIT 3を使用する → データセット全体で3行しか返されない →category_idでランク付けをパーティション分割する。 - タイ契約を定めずに
ROW_NUMBERを選択する → タイの商品が任意に切り捨てられる可能性がある → まず3行なのか、競技順位なのか、3つの異なる値なのかを定義する。 DENSE_RANKの順序付けにproduct_idを追加する → 同額の売上が同率として扱われなくなる → ビジネスランクのキーのみでランク付けし、最終的なORDER BYで表示を安定させる。- 同じ
WHERE内でrevenue_rankをフィルタリングする → 論理的な評価順序上、そのステージではウィンドウ結果が存在しない → CTEまたはサブクエリでランク付けし、外側でフィルタリングする。 - 四半期の終了日を6月30日 23:59:59にする → より高精度のタイムスタンプが欠落する可能性があり、隣接期間の境界処理が複雑になる →
[start, next_start)を使用する。 - 履歴セマンティクスを考慮せずに現在のカテゴリディメンションを結合する → 商品のカテゴリ再編によって過去のデータが書き換わる → 注文時のスナップショットまたは有効期間付きディメンションを使用し、契約を明記する。
- インデックスのみを提案する → 選択性が低いと効果がなく、誤った粒度を修正することはできない → カーディナリティ、実行計画、スピル、実際のボトルネックを測定する。
- 金額を浮動小数点型で計算する → 丸め誤差によってタイグループが変化する可能性がある → 正確な数値型（numeric）を維持し、通貨と純売上のルールを定義する。
フォローアップ質問と回答
フォローアップ1：カテゴリごとに厳密に3行が必要な場合はどうしますか？
ROW_NUMBER を使用し、売上が同じ場合の決定論的で説明可能なセカンダリ順序（product_id ASC や、明示的に指定された販売数量・発売日などのルール）を定義します。そのルールは意図的にタイを解消するため、出力契約に属します。プレースホルダーが明示的に要求されない限り、商品数が3未満のカテゴリでは返される行数も少なくなります。
フォローアップ2：「上位3位」で競技ランキング（competition ranking）を使用する場合はどうしますか？
RANK を使用します。売上 100, 100, 90, 80 には 1, 1, 3, 4 が割り当てられ、3以下でフィルタリングすると100と90のレベルが返されます。「次の商品は3位」となるように、タイの後の順位にギャップが保持されます。これは、DENSE_RANK を使用して上位3つの異なる値を取得することとは異なります。
フォローアップ3：CTEを使わずにHAVINGでランクをフィルタリングできますか？
ウィンドウ関数は論理的に後に評価されるため、同じクエリレベルの HAVING や WHERE でウィンドウ結果をフィルタリングすることはできません。同等のサブクエリであれば動作します。一部のDWHは QUALIFY をサポートしていますが、PostgreSQL 18はサポートしていません。ここでのCTEは、集計、ランク付け、フィルタリングの境界を明確にします。
フォローアップ4：翌四半期に返金が発生した場合はどうなりますか？
まず会計契約を定義します。注文帰属レポートでは元の注文四半期の数値を修正（洗替）する場合がありますが、キャッシュフローレポートでは返金が発生した四半期にマイナスのイベントとして記録する場合があります。これらには異なるイベント時刻とファクトテーブルが必要です。金額やイベント時刻のセマンティクスがない返金テーブルを単純に差し引くのではなく、追跡可能な注文・返金ファクトをモデル化し、修正・調整ポリシーを定めます。
フォローアップ5：四半期に10億件の明細行がある場合はどう対処しますか？
ランク付けの前に日付パーティションをプルーニングしステータスをフィルタリングした上で、結合キーのインデックス、集約の並列処理、ソート時のスピルを確認します。レポートが頻繁に実行され、ある程度の遅延が許容される場合は、カテゴリ・商品ごとの正確な日次集計を保持し、四半期分を合算します。遅延注文、返金、修正には、冪等なバックフィルと生ファクトとの照合（reconciliation）が必要です。インデックス、パーティショニング、事前集計の選択は、実行計画と代表的なワークロードに基づいてのみ決定できます。
フォローアップ6：結合によって売上が二重計上されたことをどうやって検知しますか？
各ステージで粒度と保存則（conservation）を確認します。対象明細の結合前後で行数が期待されるカーディナリティと一致しているか、orders.order_id が一意であるか、商品集計の合計値がフィルタされた明細金額の合計と一致しているかをチェックします。1つの注文に2つの明細がありディメンションキーが重複している反例テストを追加します。ディメンションが一意でない場合は、結合前に有効期間付きバージョンを1つ選択します。最終クエリに DISTINCT を追加しても、重複した計上を覆い隠すだけにすぎません。