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:

What's "last quarter"? Previous calendar quarter, or trailing 3 months?
How are ties handled? Top 3 strictly, or top 3 including ties?
What's "revenue"? Gross revenue, net of refunds, or contribution margin?
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

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 < upper bound is the start of this quarter. This gives exactly last quarter, no off-by-one.

Sub-step b: rank within region

WITH product_rev AS (
    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
)
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

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?"

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:

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 Scan.
If still slow, pre-aggregate revenue per product per region into a daily summary table refreshed nightly.