Home / SQL / Practice problems / Hard

Most expensive product in each category

Hard

Show category, name and price of the most expensive product in each category.

Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)

Your query
Show the expected result
categorynameprice
BooksDeep Learning (book)89.0
Electronics27-inch monitor329.0
FurnitureStanding desk549.0
GroceryCoffee beans 1 kg32.0
StationeryGel pens, pack of 1011.0

5 rows

Show a solution
WITH r AS (SELECT category, name, price, ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn FROM products) SELECT category, name, price FROM r WHERE rn = 1;

SQL used

New to SQL? The free SQL course teaches everything these problems use, with a self-checking exercise in every lesson.