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. Put the whole SQL text in the query variable and stop there. Do not run it or print anything. The check runs query on db and prints the table itself, including the header line and the row count line.
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.
Free plan: 30 tutor messages. PRO is unlimited.
