SQL Interview: Find the Top Three Products per Category

Prompt and Applicable Context

Using PostgreSQL, return the top three distinct product-revenue levels in each category for Q2 2026, including every product tied at the third level. State the data contract, write the SQL, and explain why ROWNUMBER, RANK, and DENSERANK produce different results.

The schema is:

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) )

Use this agreed contract: count only COMPLETED orders; define the quarter as the UTC half-open interval [2026-04-01, 2026-07-01); product revenue is the sum of quantity * unit_price over eligible line items; refunds are outside the supplied model; and “top three” means the three highest distinct revenue levels per category, so every product tied at the third level must be returned. Products with no eligible sales are absent, and a category with fewer than three levels returns all levels it has.

This medium question fits data analyst, data engineer, and backend roles that require analytical SQL. Public 2026 SQL interview materials still present top-N-per-group, window functions, and tie behavior as explicit exercises. There is no verifiable company attribution, so it is treated as a representative SQL interview question.

What the Interviewer Evaluates

The first signal is whether you state the output grain. One order_items row is a line item, but the target is one row per category-product pair. Ranking the raw line items gives one product several positions. Syntactically valid SQL can still answer the wrong business question.

The second signal is whether you translate “top three” into explicit tie semantics. ROWNUMBER, RANK, and DENSERANK can all order rows within a category, but they answer different questions. A strong candidate first asks whether the result needs exactly three rows, competition ranks, or three distinct revenue levels.

The third signal is knowledge of SQL's logical evaluation order. Window functions run after WHERE, GROUP BY, HAVING, and ordinary aggregation. Aggregate product revenue first, rank it at the next level, and filter the rank in an outer query. PostgreSQL cannot use the window alias in the same query level's WHERE clause.

The final signal is verifiability and maintainability. A strong answer defines the time boundary, eligible status, money type, deterministic presentation order, and test fixture. At scale, it first reduces the rows entering the ranking stage instead of offering “add an index” as an unsupported cure.

Questions to Clarify Before Answering

• Does “top three” mean three rows, three competition positions, or three distinct values? Use ROWNUMBER for three rows, RANK for competition ranking, and DENSERANK for this requirement: three distinct revenue levels with ties preserved.
• Which event and time zone assign revenue to a period? Order, payment, completion, and refund times produce different reports. This prompt uses orders.ordered_at in UTC and non-overlapping half-open intervals.
• Which statuses are eligible? Including canceled, test, or failed orders inflates revenue. This contract counts only COMPLETED.
• Where are refunds, discounts, taxes, and currencies? The supplied model contains only quantity and transaction unit price. If a refund table or multiple currencies are added, compute net revenue or convert to one currency before ranking.
• Can one product belong to multiple categories? This query treats (categoryid, productid) on the line item as the fact key. Joining today's product dimension could rewrite historical category attribution; use an order-time snapshot or effective-dated dimension when that history matters.
• Should zero-sales products appear? They do not appear here. If required, left join the aggregate to a complete category-product set and define whether zero participates in the top three levels.
• Must output order be deterministic? Rank only by revenue; adding productid to the ranking window destroys ties. Stabilize presentation separately with ORDER BY categoryid, revenuerank, productid.

30-Second Answer Framework

“I would aggregate completed UTC Q2 orders to one row per category and product, then use DENSERANK by category and descending revenue because the requirement is three distinct levels with ties. A CTE computes the rank, the outer query filters revenuerank <= 3, and final ordering only stabilizes display. I would test ties, quarter boundaries, canceled orders, and categories with fewer than three levels.”

A complete answer should also explain why aggregation precedes ranking, how the three ranking functions differ, and how to validate both the reporting contract and the execution plan at scale.

Step-by-Step Deep Answer

Step 1: Express the Business Question as Grain and Invariants

The result key is not orderid; it is (categoryid, product_id). Each key must appear once in the aggregate stage, and revenue must equal the sum of all eligible line-item amounts for that key. Ranking adds a category-local level without changing the grain.

State three invariants before writing syntax: every eligible line item contributes exactly once; the same product across different orders is combined; and a category's revenue_rank depends only on revenues inside that category. These invariants expose duplicate joins, line-item ranking, and global ranking quickly.

Step 2: Filter Facts Before Aggregating to Product Grain

Use >= for the start and < for the next quarter's start. Unlike BETWEEN or 2026-06-30 23:59:59, a half-open interval does not miss higher timestamp precision and composes cleanly with the next quarter. Explicit +00 on the timestamptz literals keeps the contract independent of the database session time zone.

After joining, filter status and time, then group by categoryid, productid. In this PostgreSQL model, quantity * unit_price remains an exact numeric expression, and SUM preserves exact money arithmetic. Do not cast it to floating point for convenience. A production revenue report also needs currency and refund semantics, but the supplied columns cannot manufacture them.

Step 3: Choose the Ranking Function from the Tie Contract

Suppose one category has product revenues 100, 100, 90, 80:

• ROW_NUMBER produces 1, 2, 3, 4. Without an additional key, the two 100 rows have an unspecified relative order. This example returns both 100 rows and one 90 row, but a tie crossing the third-row boundary would be cut arbitrarily. It guarantees three rows, not complete ties.
• RANK produces 1, 1, 3, 4. Filtering at 3 returns revenue 100 and 90, only two distinct levels.
• DENSE_RANK produces 1, 1, 2, 3. Filtering at 3 returns all four products and exactly three distinct levels.

Therefore this prompt needs DENSERANK. Its window ORDER BY must contain only revenue DESC. Adding productid means equal-revenue rows are no longer peers, silently replacing “preserve ties” with a manufactured order.

Step 4: Separate Aggregation, Ranking, and Filtering with Two CTEs

The complete PostgreSQL query is:

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;

The first stage reduces many order items to product facts. The second sorts only the product set inside each category. The outer query can then see and filter the window result. The stages mirror the metric contract, ranking contract, and output contract, so their grain and row counts can be inspected independently.

Step 5: Prove Correctness Instead of Only Showing Syntax

For any category, the first stage creates one total per product. PARTITION BY categoryid excludes every other category from comparison, and ORDER BY revenue DESC defines equal totals as one peer group. DENSERANK numbers peer groups consecutively without gaps, so <= 3 selects exactly the three highest distinct totals and every product belonging to them.

The final ORDER BY does not affect rank; it only orders returned rows. This distinction matters: ordering inside the window defines the business rank, while ordering at the end defines repeatable presentation. They may use different keys.

Step 6: Keep Performance Claims Tied to Evidence

If R eligible line items become G category-product groups, the logical work scans and aggregates R rows, then sorts G rows within categories. Sorting can be described as the sum of Gc log Gc across category partitions, but PostgreSQL may choose hash aggregation, sort aggregation, parallel execution, or disk spill. That expression is not a promise about the physical plan.

Use EXPLAIN (ANALYZE, BUFFERS) on a production-scale replica to inspect filtered rows, join strategy, aggregate rows, sort memory, and temporary files. orders(status, orderedat, orderid) and orderitems(orderid) may help filtering and joining, depending on status selectivity, distribution, and partitioning. Time partitioning can prune a large fact table. For a frequent report, consider a reconciled daily category-product aggregate. Pre-aggregation trades freshness, backfill, and correction complexity for query speed; “create a materialized view” is not a complete design.

Step 7: Prove Semantics with Small Data and Cost with Large Data

A minimal correctness fixture includes category 10 with revenues 100, 100, 90, 80, 70; expect the first four products with ranks 1, 1, 2, 3. Category 20 has only 50, 40; expect both. Include one canceled order, which must not contribute, and one order exactly at 2026-07-01 00:00:00+00, which must be excluded.

Add several orders and line items for the same product to ensure it produces one total. Give different product IDs the same total to verify a stable final order but equal rank. Scale validation then checks scanned partitions, aggregate cardinality, sort spill, elapsed time, and peak resources. Correct output and acceptable cost are separate bodies of evidence.

High-Quality Sample Answer

“I would first clarify tie behavior, time attribution, and eligible status. The requirement is the three highest distinct revenue levels per category with every third-level tie, so I would use DENSERANK; I would use ROWNUMBER only for exactly three rows.

The target grain is one row per category and product, while the source grain is one order item. My first CTE therefore joins orders to items, keeps COMPLETED orders in UTC Q2, and aggregates SUM(quantity * unitprice) by categoryid, product_id. I use April 1 inclusive and July 1 exclusive so adjacent quarters do not overlap.

The second CTE applies DENSERANK() OVER (PARTITION BY categoryid ORDER BY revenue DESC). I do not add productid to that window because it would split equal-revenue peers. PostgreSQL cannot filter a window result in the same query level's WHERE, so the outer query applies revenuerank <= 3 and then orders by category, rank, and product ID for stable display.

Revenues 100, 100, 90, 80 become ranks 1, 1, 2, 3, so all four products return. I would also test canceled orders, the quarter's right boundary, repeated line items for one product, and categories with fewer than three levels. At scale, I would measure how far filtering and aggregation reduce R source rows before using the execution plan to justify partition pruning, indexes, spill tuning, or pre-aggregation.”

This answer establishes business semantics before syntax, then supplies the query structure, correctness evidence, and a measured performance path. It does not treat a function name or an untested index suggestion as the answer.

Common Mistakes

• Ranking order items directly → one product occupies several positions and the output grain is wrong → aggregate by category and product before ranking.
• Using global ORDER BY ... LIMIT 3 → it returns three rows for the entire data set → partition ranking by category_id.
• Choosing ROW_NUMBER without a tie contract → tied products can be cut arbitrarily → define three rows, competition positions, or three distinct values first.
• Adding productid to the DENSERANK ordering → equal revenues stop being peers → rank only on business rank keys and stabilize display in the final ORDER BY.
• Filtering revenue_rank in the same WHERE → the window result does not exist at that logical stage → rank in a CTE or subquery and filter outside.
• Ending the quarter at June 30 23:59:59 → higher timestamp precision can be missed and adjacent periods are awkward → use [start, next_start).
• Joining today's category dimension without historical semantics → product recategorization rewrites history → use an order-time snapshot or effective-dated dimension and state the contract.
• Offering only an index → low selectivity may make it useless, and it cannot repair wrong grain → measure cardinalities, the plan, spill, and the actual bottleneck.
• Accumulating money in floating point → rounding can change tie groups → retain an exact numeric type and define currency and net-revenue rules.

Follow-Up Questions and Responses

Follow-up 1: What if the product requires exactly three rows per category?

Use ROWNUMBER and define an explainable deterministic secondary order for equal revenue, such as productid ASC or an explicitly requested units-sold or launch-date rule. That rule deliberately breaks ties, so it belongs in the output contract. A category with fewer than three products still returns fewer rows unless placeholders are explicitly required.

Follow-up 2: What if “top three” uses competition ranking?

Use RANK. Revenues 100, 100, 90, 80 receive 1, 1, 3, 4, and filtering at 3 returns the 100 and 90 levels. It preserves the gap after a tie, matching “the next product is third.” That is different from requesting the top three distinct values with DENSE_RANK.

Follow-up 3: Can you avoid the CTE and filter the rank in HAVING?

You cannot filter a window result in the same query level's HAVING or WHERE, because window functions are logically later. An equivalent subquery works. Some warehouses support QUALIFY, but PostgreSQL 18 does not. The CTE here makes the aggregation, ranking, and filtering boundaries explicit.

Follow-up 4: What if a refund happens in the next quarter?

Define the accounting contract first. An order-attribution report may restate the original order quarter; a cash-flow report may record a negative event in the refund quarter. Those require different event times and fact tables. Do not subtract a refund table lacking amount and event-time semantics; model traceable order and refund facts and state the restatement or adjustment policy.

Follow-up 5: How would you handle one billion quarterly line items?

Prune date partitions and filter status before ranking, then inspect join-key indexes, aggregate parallelism, and sort spill. If the report is frequent and can tolerate delay, maintain an exact daily category-product aggregate and sum it for the quarter. Late orders, refunds, and corrections need idempotent backfill and reconciliation to raw facts. Only the execution plan and a representative workload can choose among indexing, partitioning, and pre-aggregation.

Follow-up 6: How do you detect a join that doubled revenue?

Check grain and conservation at each stage: whether row counts across the eligible-item join match the expected cardinality, whether orders.order_id is unique, and whether the sum of product aggregates equals the sum of filtered item amounts. Add a counterexample with two items in one order and a duplicate dimension key. If a dimension is not unique, select one effective-dated version before joining; a final DISTINCT only hides duplicated contribution.