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…