Rank Customers Per Region With SQL Window Functions
A real data problem you debug end to end in a live cloud workspace, then show on your portfolio. No tutorial, no toy app - a broken system that behaves like production.
The scenario
The team is producing a "top 3 customers per region by total spend" report. The current SQL uses a correlated subquery per region - slow, hard to read.
The broken code you start with
-- ranks globally, not per region - wrong result
SELECT region, customer, revenue
FROM sales
ORDER BY revenue DESC;What this teaches you
What you did: Wrote a CTE that aggregates per (customer, region) into a total spend, then a second CTE applied ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_spend DESC). The final SELECT filters WHERE rn <= 3. One scan over orders, one scan over the aggregated CTE - far faster than the correlated-subquery version.
Why it matters: Window functions are the most-asked SQL topic in data-engineering interviews after JOINs. They solve "ranking", "running totals", "rolling windows", "compare to previous row" problems in one declarative pass - no application-level loops.
In the real world: Most analytics workloads (cohort analysis, funnel reports, lead/lag comparisons) are window functions all the way down. Modern DWs (Snowflake, BigQuery, DuckDB) all support the same syntax - once you know it, you're portable.
What you'll practice
- Writing ROW_NUMBER() OVER (PARTITION BY ...)
- Ranking rows within a group in one query
- Replacing slow app-side ranking with SQL
Why this impresses a hiring manager
- This is a real sql problem teams hit in production - not a synthetic puzzle.
- It shows you can diagnose and fix a Data issue in a live system end to end.
- It lands on your portfolio as a scenario a hiring manager can open and click through.
Replaced the GROUP BY + correlated subquery with a single ROW_NUMBER() OVER (PARTITION BY region ORDER BY spend DESC) - query returns the top 3 customers per region in a single scan
Keep going
Build this project free
You're in a real cloud workspace in 30 seconds. Fix it, and it lands on your portfolio.
Start this project →