Interview Question Bank · DENSE_RANK · PARTITION BY · ties
4. Top 3 products per category
MediumE-commerceQuestion #88
Concept: DENSE_RANK · PARTITION BY · tiesshow
Top-N per group means ranking inside each group with a window function, then filtering in an outer query (window functions can't go in WHERE). ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after a tie (1, 1, 3), and DENSE_RANK doesn't (1, 1, 2). With DENSE_RANK, 'top 3' means the three highest distinct values, so ties can return more than 3 rows.
Example (different data):
WITH r AS (
SELECT league, player, goals,
DENSE_RANK() OVER (PARTITION BY league ORDER BY goals DESC) AS pos
FROM scorers
)
SELECT * FROM r WHERE pos <= 3;Your task
Quillmere's homepage shows the best sellers in each category. Products tied on revenue should all get the spot.
Revenue is quantity * unit_price over completed orders (order lines without a product are ignored). Return category, product (product name), revenue and revenue_rank (DENSE_RANK of revenue within the category, highest first) for every product whose revenue_rank is 3 or better. Sort by category, revenue_rank, then product.
Starting Postgres in your browser…
Checking your access…