Top-Selling SKU in Each Store, Ties Included — SQL Problem
Category managers want each store's best seller for the festival week: the SKU with the most units sold across all of that store's sale lines.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #384
Problem statement
Category managers want each store's best seller for the festival week: the SKU with the most units sold across all of that store's sale lines. When two or more SKUs tie for the most units in a store, all of them are returned.
Return store_id, sku and units_sold. Order the rows by store_id, then by sku.
Tables
Table: SaleLine
| Column | Type |
|---|---|
| line_id | int |
| store_id | int |
| sku | varchar |
| qty | int |
| sold_on | date |
Primary key: line_id.
One row per line of a POS bill; qty is the units of that SKU on the bill.
Examples
Example 1
SaleLine
| line_id | store_id | sku | qty | sold_on |
|---|---|---|---|---|
| 1 | 1 | SNK-6001 | 6 | 2024-10-28 |
| 2 | 1 | GRO-1001 | 2 | 2024-10-28 |
| 3 | 1 | SNK-6001 | 3 | 2024-10-29 |
| 4 | 1 | DAI-2002 | 9 | 2024-10-30 |
| 5 | 2 | BEV-3001 | 4 | 2024-10-29 |
| 6 | 2 | HOM-5001 | 5 | 2024-10-30 |
| 7 | 2 | BEV-3001 | 1 | 2024-10-31 |
| 8 | 3 | PER-4001 | 2 | 2024-10-31 |
Output
| store_id | sku | units_sold |
|---|---|---|
| 1 | DAI-2002 | 9 |
| 1 | SNK-6001 | 9 |
| 2 | BEV-3001 | 5 |
| 2 | HOM-5001 | 5 |
| 3 | PER-4001 | 2 |
How to solve Top-Selling SKU in Each Store, Ties Included
Two steps: aggregate, then pick the maximum per group. GROUP BY store_id, sku turns sale lines into one total per product per store. A window function then ranks those totals inside each store: RANK() OVER (PARTITION BY store_id ORDER BY SUM(qty) DESC). Window functions run after grouping, so they can order by the aggregate directly. Keeping rnk = 1 returns every SKU tied for the top, because tied rows share rank 1.
ROW_NUMBER() is the classic mistake here: it gives tied rows different numbers in an order the engine chooses, so one of the tied SKUs would vanish — and which one could change from run to run. DENSE_RANK() would work as well as RANK() since only rank 1 is kept.
Without windows, compute the per-store maximum of the totals and join it back (or compare in HAVING with a correlated subquery). These read the sales twice but give the same rows. The final order by store and SKU makes ties appear in a fixed order.
Reference solution (MySQL)
WITH totals AS (
SELECT store_id, sku, SUM(qty) AS units_sold,
RANK() OVER (PARTITION BY store_id ORDER BY SUM(qty) DESC) AS rnk
FROM SaleLine
GROUP BY store_id, sku
)
SELECT store_id, sku, units_sold
FROM totals
WHERE rnk = 1
ORDER BY store_id, skuAnother way
SELECT store_id, sku, SUM(qty) AS units_sold
FROM SaleLine s
GROUP BY store_id, sku
HAVING SUM(qty) = (SELECT MAX(t.u) FROM (SELECT store_id, SUM(qty) AS u FROM SaleLine GROUP BY store_id, sku) t WHERE t.store_id = s.store_id)
ORDER BY store_id, skuAnother way
WITH totals AS (SELECT store_id, sku, SUM(qty) AS units_sold FROM SaleLine GROUP BY store_id, sku)
SELECT t.store_id, t.sku, t.units_sold FROM totals t
JOIN (SELECT store_id, MAX(units_sold) AS best FROM totals GROUP BY store_id) b ON b.store_id = t.store_id AND b.best = t.units_sold
ORDER BY t.store_id, t.sku← Store Stock-Out Rate From Daily Closing Stock · Cycle Count Shrinkage Value by Store and Month →