SQL Quest › SQL Interview Questions › Subqueries & CTEs
Top 10% Users by Transaction Volume
The second question candidates report from the live round: who are the top 10% by volume? Volume is the total amount_gbp of a user's completed transactions of any type. Rank users into ten equal buckets with NTILE(10) ordered by volume descending; the first bucket is the top decile.
Return user_id, country, plan (both from users) and volume_gbp (rounded to 2 decimals) for users in the top decile. Order by volume_gbp descending.
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 | user_id | ts | type | amount | currency | amount_gbp | fee_gbp | merchant_category | merchant_country | counterparty_user_id | status | decline_reason |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 2025-06-14 11:01:51 | fx_exchange | 32271.15 | JPY | 167.81 | 1.68 | NULL | NULL | NULL | completed | NULL |
| 2 | 1 | 2025-06-14 11:17:45 | bill_payment | 59.53 | EUR | 50.6 | 0 | utilities | IE | NULL | completed | NULL |
| 3 | 7 | 2025-06-16 10:06:38 | card_payment | 122.27 | EUR | 103.93 | 0 | shopping | ES | NULL | completed | NULL |
users
| user_id | signup_date | country | home_currency | plan | plan_since | kyc_verified_at | referred_by | birth_year |
|---|---|---|---|---|---|---|---|---|
| 1 | 2025-06-01 | IE | EUR | standard | 2025-06-01 | 2025-06-03 16:00:00 | NULL | 1972 |
| 2 | 2025-06-02 | FR | EUR | standard | 2025-06-02 | 2025-06-04 16:00:00 | NULL | 1979 |
| 3 | 2025-06-03 | GB | GBP | standard | 2025-06-03 | 2025-06-08 04:00:00 | NULL | 1977 |
Expected output: user_id=33, country=PL, plan=standard, volume_gbp=10711.66 ...
Hint
Concepts
SELECT CTE Window Functions NTILE JOIN SUM
Practise the topic: SQL practice questions · CTE practice · Window function practice · JOIN practice · GROUP BY exercises · Ranking function practice · Advanced SQL interview questions
In these company practice sets
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