Write SQL for the top 3 products by revenue in each region, per month.

TL;DR: Aggregate revenue to the (region, month, product) grain first, then rank within each (region, month) partition and keep the top 3. Ranking raw order lines ranks transactions, not products, and still returns three rows, so the bug ships silently.

How to approach it

Restate the grain before coding: "top 3 products within each (region, month) pair, ranked by total revenue." Then clarify ties (strict 3 rows, or all products tied at 3rd?) and confirm whether revenue needs aggregating first, order-line tables almost always do. Narrate the two-step plan: aggregate to the right grain, then rank within partitions.

A strong answer

WITH monthly_revenue AS (
  SELECT
    region,
    DATE_TRUNC('month', order_date) AS order_month,
    product_id,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY region, DATE_TRUNC('month', order_date), product_id
),
ranked AS (
  SELECT
    region,
    order_month,
    product_id,
    revenue,
    DENSE_RANK() OVER (
      PARTITION BY region, order_month
      ORDER BY revenue DESC
    ) AS rnk
  FROM monthly_revenue
)
SELECT region, order_month, product_id, revenue, rnk
FROM ranked
WHERE rnk <= 3
ORDER BY region, order_month, rnk;

For one (region, month) partition, the ranked output keeps rnk <= 3:

With DENSE_RANK, the two products tied at 700 both get rank 2 and all four rows survive the top-3 filter; ROW_NUMBER would have stopped at three.

Call out the deliberate choices:

• Aggregate first. Ranking raw order lines ranks individual transactions, not products, the most common silent failure on this question. Six order lines are enough to spring the trap (both queries executed against the same fixture): P100 sells once for 900, P205 sells twice for 400 + 350, P310 twice for 300 + 250, P044 once for 500. Rank the raw lines and the "top 3" is P100, P044, P205's larger line: P044 shows up because its single 500 transaction beats any individual line of P205's, even though P205's total of 750 makes it the real number two. Aggregate first and the top 3 is P100 (900), P205 (750), P310 (550): P044 drops to fourth. Same table, same window function, two different podiums, and the wrong one looks every bit as plausible as the right one. A product whose revenue arrives in many small orders is invisible to the unaggregated version, which is a bias, not just a bug: it systematically rewards products that sell in big-ticket lumps.
• Tie semantics. DENSE_RANK <= 3 returns everything in the top 3 revenue values (can exceed 3 rows); ROW_NUMBER <= 3 returns exactly 3 with arbitrary tie-breaking. Say which you'd pick and why, for a business report, dense rank with ties included is usually more honest; for a fixed-size leaderboard UI, row_number with a deterministic tiebreaker (ORDER BY revenue DESC, product_id).
• Partition = the "per X per Y" clause. Both region and month go in PARTITION BY; only the metric goes in ORDER BY.

On Snowflake/Databricks, collapse the second CTE with QUALIFY:

SELECT region, order_month, product_id, revenue
FROM monthly_revenue
QUALIFY DENSE_RANK() OVER (
  PARTITION BY region, order_month ORDER BY revenue DESC
) <= 3;

Mentioning QUALIFY unprompted is a strong dialect-fluency signal at Snowflake and Databricks.

What interviewers probe next

• "Returns vs gross revenue?", testing whether your SUM(amount) should net out refunds; ask what amount contains.
• "Now top 3 by units but show revenue too", only ORDER BY inside the window changes.
• "Performance on 10B rows?", pre-aggregation shrinks data before the window sort; partition pruning on order_date; in Spark this is one shuffle for the GROUP BY and one for the window, and they can share partitioning if keys align.
• "What if one region is 80% of data?", skew in the window partition; month subdivides it, or pre-aggregate harder.

Common mistakes

• Ranking before aggregating (the #1 fail).
• LIMIT 3 anywhere, it's global, not per-group.
• Forgetting month in the partition when the question says "per month."
• Filtering the window alias in the same SELECT's WHERE instead of a CTE/QUALIFY.
• Never mentioning ties, interviewers at Snowflake specifically score this.