Top-Selling SKU in Each Store, Ties Included

Medium
384.

Top-Selling SKU in Each Store, Ties Included

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

ColumnType
line_idint
store_idint
skuvarchar
qtyint
sold_ondate

Primary key: line_id.

One row per line of a POS bill; qty is the units of that SKU on the bill.

Example

The rows must come back in the order shown.

SaleLine

line_idstore_idskuqtysold_on
11SNK-600162024-10-28
21GRO-100122024-10-28
31SNK-600132024-10-29
41DAI-200292024-10-30
52BEV-300142024-10-29
62HOM-500152024-10-30
72BEV-300112024-10-31
83PER-400122024-10-31

Output

store_idskuunits_sold
1DAI-20029
1SNK-60019
2BEV-30015
2HOM-50015
3PER-40012
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
line_idint
store_idint
skuvarchar
qtyint
sold_ondate

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_idstore_idskuqtysold_on
11SNK-600162024-10-28
21GRO-100122024-10-28
31SNK-600132024-10-29
41DAI-200292024-10-30
52BEV-300142024-10-29
62HOM-500152024-10-30
72BEV-300112024-10-31
83PER-400122024-10-31

Output

store_idskuunits_sold
1DAI-20029
1SNK-60019
2BEV-30015
2HOM-50015
3PER-40012

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, sku

Another 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, sku

Another 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 →