Case 1 — Top 3 Products by Revenue per Region, Last Quarter

# Case 1 — Top 3 Products by Revenue per Region, Last Quarter > *"You have an `orders` table and a `products` table. Find the top 3 products by revenue in each region for the last quarter. The interviewer wants the full SQL."* This is the most common SQL screen problem at e-commerce and SaaS shops. 70% of analysts get the first version right and stumble on the follow-ups. --- ## Step 1 — Clarify the prompt (always do this in real interviews) Before writing SQL, ask: 1. **What's "last quarter"?** Previous calendar quarter, or trailing 3 months? 2. **How are ties handled?** Top 3 strictly, or top 3 including ties? 3. **What's "revenue"?** Gross revenue, net of refunds, or contribution margin? 4. **Is region on `orders` or `customers`?** Schema-dependent — affects the join. For this problem assume: - "Last quarter" = previous calendar quarter (Q1, Q2, Q3, or Q4). - Include ties (use DENSE_RANK). - Revenue = `SUM(quantity * unit_price)` from `orders`. - Region is on `customers`. Schema: ``` orders(order_id, customer_id, product_id, quantity, unit_price, order_date) customers(customer_id, region) products(product_id, name) ``` ## Step 2 — Build it up **Sub-step a: revenue per product per region in the date range** ```sql SELECT c.region, o.product_id, SUM(o.quantity * o.unit_price) AS revenue FROM orders o JOIN customers c ON c.customer_id = o.customer_id WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE) GROUP BY c.region, o.product_id; ``` The `DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months'` is "the start of the previous quarter". The strict ` = DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE) GROUP BY c.region, o.product_id ) SELECT region, product_id, revenue, DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk FROM product_rev; ``` **Sub-step c: filter to top 3 and join product names** ```sql WITH product_rev AS (...), ranked AS ( SELECT *, DENSE_RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk FROM product_rev ) SELECT r.region, p.name AS product_name, r.revenue, r.rnk FROM ranked r JOIN products p ON p.product_id = r.product_id WHERE r.rnk <= 3 ORDER BY r.region, r.rnk; ``` That's the full answer. ## Step 3 — Explain your tradeoffs Even after producing correct SQL, real interviewers want to hear: - **Why DENSE_RANK and not ROW_NUMBER?** "Including ties was the spec — DENSE_RANK handles that. If they wanted exactly 3 rows per region, I'd use ROW_NUMBER with a deterministic tiebreaker." - **What about ties at the boundary?** "If 5 products tie for rank 3, all 5 return. That matches the spec. If we needed exactly 3 per region, we'd add a tiebreaker like `ORDER BY revenue DESC, product_id ASC` and use ROW_NUMBER." - **Performance?** "The CTE materializes the aggregated revenue once. The window function is cheap. If `orders` is huge, the WHERE on order_date drives the speed — needs an index on order_date." ## Common follow-ups ### Follow-up 1: "What if products can be in multiple regions and we want the global top 3?" ```sql WITH product_rev AS ( SELECT o.product_id, SUM(o.quantity * o.unit_price) AS revenue FROM orders o WHERE o.order_date >= DATE_TRUNC('quarter', CURRENT_DATE) - INTERVAL '3 months' AND o.order_date < DATE_TRUNC('quarter', CURRENT_DATE) GROUP BY o.product_id ) SELECT p.name, pr.revenue FROM product_rev pr JOIN products p ON p.product_id = pr.product_id ORDER BY pr.revenue DESC LIMIT 3; ``` Drop the region partition and switch to a global LIMIT. ### Follow-up 2: "Now show me the top 3 per region AND the global top 3, in one result." Use UNION ALL: ```sql WITH product_rev_region AS (...), -- as before product_rev_global AS ( SELECT NULL AS region, product_id, SUM(quantity * unit_price) AS revenue FROM orders WHERE ... GROUP BY product_id ), ranked_region AS (...), ranked_global AS (...) SELECT 'by-region' AS scope, region, product_name, revenue FROM ranked_region WHERE rnk <= 3 UNION ALL SELECT 'global' AS scope, NULL, product_name, revenue FROM ranked_global WHERE rnk <= 3; ``` ### Follow-up 3: "Speed it up. Last quarter has 50M orders." - Make sure there's an index on `orders.order_date` (the most selective filter). - Make sure there's an index on `orders.customer_id` and `customers.customer_id` for the join. - Confirm with EXPLAIN that you see an Index Scan on order_date, not a Seq S