SQL ยท Lesson 97 of 126
Interview Questions, Part 2
Window function classics.
The second round of interview questions usually tests window functions. Top N per group, percentages of a total, gaps between events, and retention. These are exactly the questions data teams answer every day.

- Round two: window functions
- Top N, percentages
- Gaps, retention
Best customer in each country
Find the biggest spending customer in each country. It's the top N per group pattern, with spending worked out first. Spend adds up each customer's money. Ranked numbers them inside each country. Then we keep number one. Run it. Yara Ali in the UK is the biggest spender overall. Every country has its own champion.
query.sqlsql
WITH spend AS (
SELECT c.country,
c.first_name || ' ' || c.last_name AS customer,
SUM(oi.quantity * b.price) AS spent
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
JOIN order_items AS oi ON oi.order_id = o.order_id
JOIN books AS b ON b.book_id = oi.book_id
WHERE o.status IN ('delivered', 'shipped')
GROUP BY c.customer_id
),
ranked AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY country
ORDER BY spent DESC) AS rn
FROM spend
)
SELECT country, customer, ROUND(spent, 2) AS spent
FROM ranked
WHERE rn = 1
ORDER BY spent DESC;Output
โโโโโโโโโโโโโฌโโโโโโโโโโโโโโฌโโโโโโโโโ โ country โ customer โ spent โ โโโโโโโโโโโโโผโโโโโโโโโโโโโโผโโโโโโโโโค โ UK โ Yara Ali โ 445.27 โ โ Canada โ Farah Novak โ 415.25 โ โ Australia โ Farah Khan โ 385.28 โ โ USA โ Sam Silva โ 362.79 โ โ Germany โ Quinn Chen โ 257.9 โ โ Ireland โ Jade Chen โ 241.39 โ โโโโโโโโโโโโโดโโโโโโโโโโโโโโดโโโโโโโโโ
Summary
Spend, rank, filter. That three step pattern answers top N per group, however complicated the spending is. Next, percentages.
Key takeaways- Step 1: calculate
- Step 2: rank per group
- Step 3: keep the top
Next up: Percentages of a Total โ SUM() OVER ()