HardSQL challenge #198WINDOW FUNCTIONPARTITIONRANKING

Rank Products by Price Within Each Category

Add a rank to every product, ordered by price and restarting for each 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

Rank products by price within each category.

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

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	rnk
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
10	Magnetic Phone Mount Pro	Automotive	1599.08	495.71	411	SKU-000010	active	3.1	176	4
77	Leather Steering Wheel Cover	Automotive	1527.59	641.59	105	SKU-000077	active	3	258	5

First 5 of 120 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

Ranking rows with RANK() or DENSE_RANK() and handling ties
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

PARTITION BY category

Show the step-by-step approach

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

Read from the products table.
Rank the rows with RANK(), ordered by price descending, restarting for each category. Tied rows share a rank and the next rank is skipped.

Model solution

Checking your session…

What to watch for

PARTITION BY restarts the numbering for each category while keeping every row; GROUP BY would collapse them. With RANK(), two products at the same price share a rank and the next rank is skipped.

RANK() and DENSE_RANK(). RANK() skips numbers after a tie (1, 1, 3) and DENSE_RANK() does not (1, 1, 2). Filtering on rank <= N returns more than N rows when there are ties.
PARTITION BY. PARTITION BY is not GROUP BY: it keeps every row and only decides which rows a window function looks at.

Solve This SQL Challenge

Dense Rank Salaries Within Each DepartmentSimilar levelHard
Rank All Employees by Salary with RANK()Similar levelHard
Rank Orders by Total with DENSE_RANK()Similar levelHard
Find the Top 2 Salaries in Each DepartmentSimilar levelHard
Find the Second Highest Salary in Each DepartmentSimilar levelHard

Same technique, other tables

More practice on this pattern with different columns:

Keep learning

Lesson: RANK & DENSE_RANK
Lesson: Window Functions
All SQL practice challenges, by difficulty and topic
SQL interview questions with answers
SQL Playground: run any query on the same database