A Ranking per Group: PARTITION BY

In the last lesson, ROW_NUMBER() OVER (ORDER BY price DESC) numbered all the products in one list. Often you want a ranking per group instead, where the priciest product of each category is number 1. Write PARTITION BY inside the OVER (…): it splits the rows into one partition per value, and the numbering starts again at 1 in each one.

Unlike GROUP BY, nothing is folded together: every row stays and gets one more column. For example, this numbers the customers of each city in alphabetical order:

SELECT city, name,
    ROW_NUMBER() OVER (PARTITION BY city ORDER BY name) AS n
FROM customers

RANK or ROW_NUMBER? On a tie, RANK() gives the tied rows the same number and then skips: prices 29, 29, 15 rank 1, 1, 3. ROW_NUMBER() always counts 1, 2, 3. For this lesson, a hidden setup adds a Vase (home, 29) to products, so it ties with the Lamp. The Gift card has no category (NULL), and the NULL rows form a partition of their own.

The check runs show(db, query) and prints:

category | name | price | row_num | price_rank
NULL | Gift card | 50 | 1 | 1
home | Lamp | 29 | 1 | 1
home | Vase | 29 | 2 | 1
home | Plant | 15 | 3 | 3
kitchen | Teapot | 22 | 1 | 1
kitchen | Mug | 12 | 2 | 2
office | Stapler | 9 | 1 | 1
office | Notebook | 7 | 2 | 2
office | Pen | 2 | 3 | 3
(9 rows)

Your task: select category, name, price from products, plus two columns over the same window, partitioned by category and ordered by price from highest: ROW_NUMBER() named row_num, then RANK() named price_rank.

Assignment
Follow the Coding Tutorial to rank the products inside each category with RANK() and ROW_NUMBER().

Hint
Both columns use the same OVER (…): PARTITION BY first, then ORDER BY. Every column line except the last one ends with a comma.