Start learning free

LESSON 25 OF 66 · SQL: INTERVIEW QUESTIONS

Top Customer of Each Month in Python

Practice

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.


STEP 1 OF 7
    Ctrl + Enter to check

    Sqlq Top Customer Month in Python

    Keep going with Python1,600+ step-by-step lessons, from your first line of code to coding interviews. Free to start.
    Start learning free