Best seller of each category in each month

Medium

Question

The sales table has one row for each sale line: the sold_on date, the category and the product of the item, and the number of units sold. A product has many rows in a month.

For every month and every category, find the best-selling product: the product with the most units in total in that month. If two or more products tie for the most units, return all of them.

Return month (like 2026-01), category, product and units (the total units of the product in that month). The order of the rows does not matter.

window function
rank
top n per group
ties
cte
strftime
group by

Read first: Lesson 48, SQL patterns that interviews repeat

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

sales5 columns · 16 rowsnot run yet

CREATE TABLE sales (id INTEGER PRIMARY KEY, sold_on TEXT NOT NULL, category TEXT NOT NULL, product TEXT NOT NULL, units INTEGER NOT NULL);

Example rows of the sales table
id	sold_on	category	product	units
1	2026-01-04	Snacks	Chips	40
2	2026-01-19	Snacks	Chips	30
3	2026-01-07	Snacks	Biscuits	50
4	2026-01-25	Snacks	Biscuits	10
5	2026-01-12	Snacks	Namkeen	55
6	2026-01-05	Drinks	Cola	60
7	2026-01-28	Drinks	Cola	30
8	2026-01-15	Drinks	Lassi	90
9	2026-01-30	Drinks	Juice	20
10	2026-02-03	Snacks	Namkeen	45
11	2026-02-21	Snacks	Namkeen	35
12	2026-02-10	Snacks	Chips	45
13	2026-02-02	Drinks	Lassi	35
14	2026-02-24	Drinks	Lassi	25
15	2026-02-14	Drinks	Cola	55
16	2026-02-18	Drinks	Juice	20

About this SQL

SQLite has no QUALIFY, so a filter on a window function needs a CTE or a subquery. RANK() gives tied rows the same number, so WHERE rnk = 1 keeps all tied products, while ROW_NUMBER() would keep only one. strftime('%Y-%m', x) cuts the month out of a date. In SQLite you can use a select alias (month) in GROUP BY, which Postgres also allows.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready