HardSQL challenge #199WINDOW FUNCTIONPARTITIONFILTER

Find the Top 3 Most Expensive Products in Each Category

Return the three highest-priced products of every category.

The challenge runs on the SQLabHub practice database (SQLite) and uses the products table. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find top 3 expensive products in each category.

A correct answer returns 30 rows with 11 columns: id, name, category, price, cost, stock, sku, status, rating, review_count, rn.

Tables used in this challenge

`products` 120 rows

id INTEGER (PK) · name TEXT · category TEXT · price REAL · cost REAL · stock INTEGER · sku TEXT · status TEXT · rating REAL · review_count INTEGER

First 3 rows of the products table
id	name	category	price	cost	stock	sku	status	rating	review_count
1	Play Kitchen Deluxe	Toys	1786.38	589.51	257	SKU-000001	active	4	57
2	Micro-Cut Paper Shredder 8-Sheet	Office Supplies	907.93	281.46	131	SKU-000002	active	2.5	447
3	4K Dual Dash Camera	Automotive	568.89	204.8	176	SKU-000003	active	4.1	29

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
id	name	category	price	cost	stock	sku	status	rating	review_count	rn
61	2000A Jump Starter Pack	Automotive	1948.86	915.96	338	SKU-000061	active	4.3	43	1
22	Back Seat Organizer	Automotive	1804.31	685.64	428	SKU-000022	active	3	131	2
60	Leather Steering Wheel Cover	Automotive	1727.7	915.68	10	SKU-000060	active	2.6	400	3
110	The Manager's Path	Books	1979.28	989.64	48	SKU-000110	active	4.9	381	1
35	Clean Code (Martin)	Books	1891.09	699.7	50	SKU-000035	active	3.7	171	2

First 5 of 30 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.

What you practise

Filtering rows with a WHERE condition
Querying the result of another query with a subquery in FROM
Numbering rows with ROW_NUMBER() to pick one or a few per group
Restarting a window calculation for each group with PARTITION BY

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

ROW_NUMBER

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

Build an inner query first (a derived table):
1. Read from the products table.
2. Number the rows with ROW_NUMBER(), ordered by price descending, restarting for each category.
Filter in WHERE: keep the rows where rn is at most 3. The filter sits in the outer query because a window function cannot be tested in the WHERE clause of the query that computes it.

Model solution

Checking your session…

What to watch for

A window function cannot be used in the WHERE clause of the query that defines it, so the numbering is done in an inner query and filtered outside. ROW_NUMBER() always gives exactly three per category; RANK() would return more when prices tie.

ROW_NUMBER(). ROW_NUMBER() breaks ties arbitrarily. When the ORDER BY inside OVER does not identify one row, the row that gets number 1 can change between runs.
PARTITION BY. PARTITION BY is not GROUP BY: it keeps every row and only decides which rows a window function looks at.
Derived table. Columns computed in the inner query are only visible outside under their alias, so every calculated column there needs a name.

Solve This SQL Challenge