Write a SQL query to find the top 5 products by sales per region.

Aggregate to the grain you are ranking on, rank within each region, then filter.

SELECT region, product_id, total_sales
FROM (
  SELECT region,
         product_id,
         SUM(sales) AS total_sales,
         ROW_NUMBER() OVER (PARTITION BY region
                            ORDER BY SUM(sales) DESC) AS rn
  FROM   sales
  GROUP  BY region, product_id
) ranked
WHERE rn <= 5
ORDER BY region, total_sales DESC;

Aggregating and ranking in one pass

Notice that the window function orders by SUM(sales) directly. Window functions are evaluated after GROUP BY, so the aggregate is already available and no extra subquery is needed. This surprises people who expect to have to nest twice.

Choosing the ranking function

ROW_NUMBER returns exactly five rows per region, breaking ties arbitrarily. That is usually what "top 5" means for a dashboard with fixed layout, but it means two products with identical sales can swap places between runs. Add a deterministic tiebreaker — ORDER BY SUM(sales) DESC, product_id — so results are reproducible.

If a tie at fifth place should include both products, use RANK instead and accept a variable row count. DENSE_RANK returns the top five distinct sales values, which can be many more than five rows. Pick deliberately and state the choice.

Shorter syntax where available

Snowflake, BigQuery and DuckDB support QUALIFY, which filters on a window function without the subquery:

SELECT region, product_id, SUM(sales) AS total_sales
FROM   sales
GROUP  BY region, product_id
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY SUM(sales) DESC) <= 5;

PostgreSQL and MySQL have no QUALIFY, so the subquery form is the portable answer.

Performance

The window sorts within each partition, so cost scales with the number of distinct region-product pairs rather than raw row count — the GROUP BY has already collapsed the data. Partitioning or clustering the source table by region lets the engine process regions in parallel.

In the interview, also mention that this exact pattern generalises to any "top N per group" problem by changing the partition key.