SQL Quest › SQL Interview Questions › Subqueries & CTEs

How Concentrated Is the Spend?

HardProQuerying BasicsSubqueries & CTEsWindow FunctionsConditional LogicAggregation & Grouping

"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

This is a Pro challenge — the hint, the step-by-step tutor and the reference solution open in the app.

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

Read the concept: Recursive CTE explained

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.

Preparing for Capital One? How the CodeSignal data analyst assessment works.

How Concentrated Is the Spend?

Schema

Hint

Concepts

In these company practice sets

Related questions