SQL Quest › SQL Interview Questions › Conditional Logic

Ticket-Size Mix by Category

MediumFreeQuerying BasicsConditional LogicJoinsAggregation & Grouping

Pivot the ledger into spend segments. A dataset question on a card screen often wants one row per category with the transaction mix across amount buckets as COLUMNS — that is conditional aggregation: SUM(CASE WHEN ... THEN 1 ELSE 0 END) once per bucket. Buckets: small below 25, mid from 25 up to but not including 100, large 100 and above.

Return category, small_txns, mid_txns, large_txns, and total_txns (COUNT of all transactions in the category). Order by total_txns descending, then category ascending.

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 26 132 289 447, Travel 28 101 244 373 ...

Hint

Three SUM(CASE ...) columns and a COUNT(*) in the same SELECT, GROUP BY m.category. The boundaries are amount < 25, amount >= 25 AND amount < 100, amount >= 100 — the three should add up to total_txns.

Concepts

SELECT CASE JOIN GROUP BY SUM Pivot / Conditional Aggregation

Practise the topic: SQL practice questions · CASE WHEN practice · JOIN practice · GROUP BY exercises

Read the concept: SQL CASE WHEN tutorial

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.

Ticket-Size Mix by Category

Schema

Hint

Concepts

In these company practice sets

Related questions