AI Hints
Homeโ€บSQL Tutorialโ€บInterview Questions, Part 2
SQL ยท Lesson 97 of 126

Interview Questions, Part 2

Window function classics.

โฑ 2 min readโ–ถ Video at 2:17:24๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Interview Questions, Part 2
From the video: Interview Questions, Part 2
  • 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 ()