Top Customer of Each Month

A favorite interview question: who was the top customer of each month, by revenue? In one sentence: total each customer's revenue per month in one named query, rank the customers inside each month in a second one, and keep rank 1.

A window function ranks rows without merging them. RANK() OVER (PARTITION BY … ORDER BY … DESC) restarts the numbering for each value of the PARTITION BY column, and tied rows share a rank. For example, this ranks the products inside each category, most expensive first:

SELECT category, name, price,
       RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products

WHERE runs before window functions, so a window column can't be filtered in its own query. Rank in one named query and filter in the next: in WITH a AS (…), b AS (…) SELECT … FROM b, each named query reads the ones above it like a table.

For this lesson the check adds one paid March order, order 8: Chloe Ng, 2026-03-20, one item at 12 and one at 22. That makes her March revenue 34, the same as Ana Pop's, so March has two top customers. Your query must print:

month | name | revenue
2026-03 | Ana Pop | 34
2026-03 | Chloe Ng | 34
2026-04 | Ana Pop | 41
2026-05 | Dan Weber | 73
(4 rows)

Your task: your file already holds the start of monthly: its FROM, joins and WHERE. Give it a SELECT with the month (strftime('%Y-%m', ordered)), the name and the revenue, and a GROUP BY on month and name. Add a second named query ranked with each row's RANK() in its month, named month_rank, and select the rows of rank 1 from it.

Assignment
Follow the Coding Tutorial to finish monthly, add ranked, and keep each month's rank-1 customers.

Hint
The revenue is SUM(quantity * unit_price). Use RANK(), not ROW_NUMBER(): tied customers must both get rank 1.