Top N per Group

An interviewer asks: “Find the 2 best-selling products in each category.” This is the top N per group question, and real reports are full of it: the top 3 products per region, each customer's latest order, the best day of each month. Get it wrong and whole groups quietly vanish from a dashboard.

The starting code already totals the paid units of each product in orders.csv and sorts them by category, then units. It prints 8 rows:

category | product | units
gifts | Gift card | 415
home | Plant | 413
home | Lamp | 366
kitchen | Teapot | 379
kitchen | Mug | 362
office | Notebook | 399
office | Stapler | 364
office | Pen | 334
(8 rows)

The trap: ORDER BY units DESC LIMIT 2. LIMIT cuts the whole result and knows nothing about categories, so it gives two rows in all, Gift card (415) and Plant (413): kitchen and office are gone.

The right way: number the rows inside each category, then keep numbers 1 and 2. ROW_NUMBER() numbers rows 1, 2, 3… in the order set by ORDER BY inside OVER ( ), and PARTITION BY starts the count again at 1 in each group. The window runs after GROUP BY, so it can sort by the total units, and QUALIFY keeps the rows whose number passes. The same idea finds each customer's latest order line:

SELECT customer_id, order_id, date
FROM 'orders.csv'
QUALIFY ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY date DESC
) = 1

Interviewers often follow up with ties: ROW_NUMBER never gives two rows the same number, so a tie for second place keeps only one of them, in no set order. RANK() gives tied rows the same number and keeps both.

Your task: below GROUP BY, add a QUALIFY that numbers the products in each category from the most units down and keeps numbers 1 and 2. Gifts has only one product and office drops Pen, so 7 rows remain, and the check prints them:

category | product | units
gifts | Gift card | 415
home | Plant | 413
home | Lamp | 366
kitchen | Teapot | 379
kitchen | Mug | 362
office | Notebook | 399
office | Stapler | 364
(7 rows)

Assignment
Follow the Coding Tutorial to add QUALIFY ROW_NUMBER() OVER ( … ) <= 2, then fill the window: first ORDER BY units DESC, then PARTITION BY category above it.

Hint
Watch the rows after each step. With only ORDER BY units DESC inside OVER, you get the LIMIT 2 answer, Gift card and Plant; PARTITION BY category is what makes it two per category.