Top Three Sellers per Category in Q4, With Ties
For the festive-season awards, each category honours its top sellers by revenue (quantity * unit_price) over order lines dated in Q4 2024 (1 October to 31…
- Difficulty: Hard
- Topics: Window Functions, Joins, Aggregation
- Dialect: MySQL
- Problem: #119
Problem statement
For the festive-season awards, each category honours its top sellers by revenue (quantity * unit_price) over order lines dated in Q4 2024 (1 October to 31 December). Sellers with equal revenue share a rank, and ranks are dense — after two sellers tied at rank 1 the next is rank 2. Every seller ranked 1, 2 or 3 is honoured, however many that is.
Return category, store_name, revenue and category_rank, ordered by category, then category_rank, then store_name. Lines outside Q4 are ignored; a seller's revenue in a category counts only their lines in that category.
Tables
Table: sellers
| Column | Type |
|---|---|
| seller_id | int |
| store_name | varchar |
Primary key: seller_id.
One row per seller; store names are unique.
Table: order_lines
| Column | Type |
|---|---|
| line_id | int |
| seller_id | int |
| category | varchar |
| quantity | int |
| unit_price | int |
| order_date | date |
Primary key: line_id.
One row per delivered order line; seller_id is always in sellers.
Examples
Example 1
sellers
| seller_id | store_name |
|---|---|
| 1 | UrbanKart |
| 2 | StyleStreet |
| 3 | Lakshmi Textiles |
| 4 | Patel Handlooms |
| 5 | GadgetHub |
order_lines
| line_id | seller_id | category | quantity | unit_price | order_date |
|---|---|---|---|---|---|
| 1 | 1 | Fashion | 2 | 1500 | 2024-10-05 |
| 2 | 2 | Fashion | 1 | 3000 | 2024-11-11 |
| 3 | 3 | Fashion | 1 | 2000 | 2024-12-01 |
| 4 | 4 | Fashion | 3 | 500 | 2024-10-20 |
| 5 | 4 | Fashion | 2 | 4000 | 2024-09-30 |
| 6 | 5 | Fashion | 1 | 900 | 2024-12-31 |
| 7 | 5 | Electronics | 2 | 2500 | 2024-11-02 |
| 8 | 1 | Electronics | 1 | 4999 | 2025-01-01 |
Output
| category | store_name | revenue | category_rank |
|---|---|---|---|
| Electronics | GadgetHub | 5000 | 1 |
| Fashion | StyleStreet | 3000 | 1 |
| Fashion | UrbanKart | 3000 | 1 |
| Fashion | Lakshmi Textiles | 2000 | 2 |
| Fashion | Patel Handlooms | 1500 | 3 |
How to solve Top Three Sellers per Category in Q4, With Ties
First reduce the lines to revenue per seller per category, keeping only Q4 dates. The date filter is inclusive at both ends — 1 October and 31 December count, 30 September and 1 January do not — and it runs before grouping, so Patel Handlooms' September line in the example adds nothing.
Then rank inside each category with DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) and keep ranks up to 3. The choice of ranking function is the heart of the question: ROW_NUMBER() would honour exactly three sellers and break ties arbitrarily; RANK() would give 1, 1, 3 and stop a seller from being "second"; DENSE_RANK() gives 1, 1, 2, 3 — UrbanKart and StyleStreet both earn ₹3,000 in Fashion and share rank 1, so four Fashion sellers are honoured.
The correlated alternative computes the dense rank by definition: one plus the number of distinct revenues strictly above. It is quadratic per category but needs no window functions. The final ORDER BY names the store as the last key so tied sellers come out in a fixed order.
Reference solution (MySQL)
WITH revenue AS (
SELECT l.category, s.store_name, SUM(l.quantity * l.unit_price) AS revenue
FROM order_lines l
JOIN sellers s ON s.seller_id = l.seller_id
WHERE l.order_date BETWEEN '2024-10-01' AND '2024-12-31'
GROUP BY l.category, s.seller_id, s.store_name
), ranked AS (
SELECT category, store_name, revenue,
DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS category_rank
FROM revenue
)
SELECT category, store_name, revenue, category_rank
FROM ranked
WHERE category_rank <= 3
ORDER BY category, category_rank, store_nameAnother way
WITH revenue AS (
SELECT l.category, s.store_name, SUM(l.quantity * l.unit_price) AS revenue
FROM order_lines l JOIN sellers s ON s.seller_id = l.seller_id
WHERE YEAR(l.order_date) = 2024 AND QUARTER(l.order_date) = 4
GROUP BY l.category, s.seller_id, s.store_name
)
SELECT category, store_name, revenue, category_rank FROM (
SELECT r.category, r.store_name, r.revenue,
(SELECT COUNT(DISTINCT x.revenue) FROM revenue x WHERE x.category = r.category AND x.revenue > r.revenue) + 1 AS category_rank
FROM revenue r
) t
WHERE category_rank <= 3
ORDER BY category, category_rank, store_name← Median Delivery Days per Courier Partner · Longest Daily Selling Streak per Seller →