SQL Quest › SQL Interview Questions › Joins
Transaction Share by Merchant Category
Where does the card get used? Category lives on the merchants table, not on the transaction, so join on merchant_id first. Then count transactions per category and express each count as a share of ALL transactions — a scalar subquery (SELECT COUNT(*) FROM transactions) in the denominator does it in one pass.
Return category, txn_count, and share_pct (100.0 × count ÷ total transactions, rounded to 2 decimals). Order by txn_count descending, then category ascending. Multiply by 100.0, not 100 — integer division would round every share to 0.
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 |
merchants
| merchant_id | name | category | country | risk_tier |
|---|---|---|---|---|
| 1 | BigBox Mart | Grocery | TR | high |
| 2 | Quick Stop | Electronics | JP | high |
| 3 | Aurora Cafe | Travel | TR | high |
Expected output: Clothing 447 20.65, Travel 373 17.23 ...
Hint
SELECT, JOIN, GROUP BY, and open the hint there if you stall.Concepts
SELECT JOIN GROUP BY Subquery ROUND JOIN + GROUP BY
Practise the topic: SQL practice questions · JOIN practice · GROUP BY exercises · CTE practice
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