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". Free accounts get 10 free challenge solves, and this can be one of them.
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
Read the concept: LEFT JOIN vs INNER JOIN
The trap to watch for: Average of averages
In these company practice sets
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.
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