Drill 15 of 17 in E-commerce questions
0

Interview Question Bank · RANK vs DENSE_RANK vs ROW_NUMBER · PARTITION BY

15. Top 3 products per category by units, ties kept

MediumE-commerceQuestion #279

Concept: RANK vs DENSE_RANK vs ROW_NUMBER · PARTITION BYshow

The three ranking functions differ only on ties. ROW_NUMBER never ties, so it silently cuts one of two equal products. RANK gives ties the same number and then skips (1, 2, 2, 4), while DENSE_RANK doesn't skip (1, 2, 2, 3). 'Top 3 with ties' usually means RANK <= 3: every product with fewer than three products strictly ahead of it.

Example (different data):

WITH r AS (
  SELECT league, team, SUM(goals) AS goals,
    RANK() OVER (PARTITION BY league ORDER BY SUM(goals) DESC) AS goal_rank
  FROM match_stats
  GROUP BY league, team
)
SELECT * FROM r WHERE goal_rank <= 3;

Your task

Brindlewood Market's merchandising team is filling homepage spots with up to three best sellers per category by units sold. A product tied with one that makes the cut shouldn't be left off.

Using completed orders only, total each product's units_sold (sum of quantity). Return category, product (product name), units_sold and units_rank (RANK within the category, most units first: ties share a rank and the next rank is skipped) for every product with units_rank 3 or better. Sort by category, units_rank, then product.

Starting Postgres in your browser…

Checking your access…