Top 2 per Category in Python

The 2 dearest products in each category: a classic question. You already have the tool for the first half. ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) numbers the products from 1 in each category. The second half is keeping the rows numbered 1 and 2.

The catch: a query runs its WHERE before it works out its window columns. So WHERE row_num <= 2 can't go in the same query as the ROW_NUMBER(): the number doesn't exist yet there. The fix is a CTE. Number the rows inside it, then filter in the main query, where the number is an ordinary column. The same pattern finds each customer's latest order:

query = (
    "WITH numbered AS ( "
        "SELECT customer_id, id, ordered, "
            "ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered DESC) AS n "
        "FROM orders "
    ") "
    "SELECT customer_id, id, ordered "
    "FROM numbered "
    "WHERE n = 1 "
)

For this lesson, a hidden setup adds three products to shop.db: Kettle (35, kitchen), Bowl (8, kitchen) and Candle (6, home). Gift card has no category: PARTITION BY puts it in a group of its own, NULL. Your query must print:

category | name | price | row_num
NULL | Gift card | 50 | 1
home | Lamp | 29 | 1
home | Plant | 15 | 2
kitchen | Kettle | 35 | 1
kitchen | Teapot | 22 | 2
office | Stapler | 9 | 1
office | Notebook | 7 | 2
(7 rows)

Your task: in a CTE named ranked, select each product's category, name and price, with a row_num that starts again at 1 in each category, dearest first. Then, in the main query, read category, name, price, row_num from ranked and keep the rows where row_num is 2 or less.

Assignment
Follow the Coding Tutorial to write the CTE ranked first, then the main query that reads it and keeps the top 2 of each category.

Hint
Inside the window, PARTITION BY category comes before ORDER BY price DESC. Put the WHERE in the main query, below FROM ranked, not inside the CTE.