Return each categorys second most expensive product: category, productname, price. Table definitions (from the source…
SQLLevel: IntermediateRanking and Top-N
Return each categorys second most expensive product: category, product_name, price.
Table definitions (from the source exercise):
CREATE TABLE products(product_id INTEGER PRIMARY KEY, product_name TEXT, category TEXT, price REAL);
Hints
- Define one output row and the source-table keys.
- Check duplicates, NULLs and the order of filtering and aggregation.
Answer
Approach: ROW_NUMBER by price descending within category; keep rn = 2.
SELECT category, product_name, price
FROM (
SELECT p.*, ROW_NUMBER() OVER (PARTITION BY category
ORDER BY price DESC) AS rn
FROM products p
) x
WHERE rn = 2;
The source uses SQLite with a MySQL compatibility layer. Date and string functions are dialect dependent; the query is preserved as published.
Source and licence: SQL Practice Lab — Ebrahim Sharifi · MIT