SQL Quest › SQL Interview Questions › Subqueries & CTEs
How Concentrated Is the Spend?
"What share of spend comes from the top 10% of cardholders?" is a portfolio question, and the cut-off has to be computed, not typed. Hard-coding rn <= 20 answers it for a book of exactly 200 accounts and quietly stops being right the day the book grows.
COUNT(*) OVER () — a window with an empty OVER — gives you the number of accounts on every row of the same pass that ranks them. Integer division by 10 is then the decile boundary, and a CASE turns the rank into a segment label you can group by.
Build it in two CTEs: spend per account, then rank plus the row count. Return segment ('top_10_pct' or 'other_90_pct'), accounts, segment_spend (rounded to 2 decimals), pct_of_total_spend (100.0 × the segment ÷ the ledger's total spend, rounded to 2 decimals) and avg_spend_per_account (rounded to 2 decimals). Two rows. Order by segment ascending.
Rank with ORDER BY spend DESC, account_id ASC. Without the tiebreaker two accounts on identical spend could land either side of the boundary and the answer would move between runs.
Solve it in the browser editor →
Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".
Schema
transactions
| txn_id | account_id | amount | txn_at | merchant_id | lat | lng | status |
|---|---|---|---|---|---|---|---|
| 1 | 149 | 151.84 | 2026-03-04T12:39:39.078Z | 24 | 40.342 | -74.4092 | completed |
| 2 | 21 | 10.72 | 2026-03-04T13:04:50.641Z | 7 | 52.9366 | 13.3229 | completed |
| 3 | 35 | 158.78 | 2026-03-04T13:27:15.528Z | 25 | 52.7397 | 13.3134 | completed |
Expected output: other_90_pct 180 246477.25 68.28 1369.32; top_10_pct 20 114488.62 31.72 5724.43
Hint
Concepts
SELECT CTE Window Functions ROW_NUMBER CASE Subquery Aggregation Window Functions + CTE
Practise the topic: SQL practice questions · CTE practice · Window function practice · CASE WHEN practice · GROUP BY exercises · Ranking function practice · Advanced SQL interview questions
In these company practice sets
Capital One · Ramp · Bloomberg
A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.
Related questions
Where would this cost you points in an interview?
Ten questions, no signup: a Skillmap across nine SQL skills and the one to fix first.
Take the readiness test