SQL Quest › SQL Interview Questions › Window Functions

Busiest Merchants, and What a Tie Does to the Rank

MediumFreeQuerying BasicsWindow FunctionsJoinsAggregation & Grouping

RANK() and ROW_NUMBER() differ only when there is a tie — and this table has four of them. Three merchants sit on exactly 88 transactions. RANK() gives all three the number 10 and then jumps to 13. ROW_NUMBER() gives 10, 11, 12 and has to break the tie somehow — if you do not tell it how, the engine picks, and your answer changes between runs.

Count transactions per merchant and return merchant_id, name, txn_count, busy_rank (RANK() over txn_count descending — ties share a rank) and row_num (ROW_NUMBER() over txn_count descending, merchant_id ascending — always distinct). All 25 merchants. Order by txn_count descending, then merchant_id ascending.

Window functions run after the GROUP BY, so RANK() OVER (ORDER BY COUNT(*) DESC) is legal in the same SELECT that does the counting.

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: 11 RideHail Inc 101 1 1; … 1 BigBox Mart 88 10 10; 8 Tech Den 88 10 11; 18 TrainHub 88 10 12; 6 Subscript Co 85 13 13; …

Hint

RANK() OVER (ORDER BY COUNT(*) DESC) AS busy_rank and ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC, m.merchant_id) AS row_num, both in the SELECT of a GROUP BY m.merchant_id, m.name query. The tiebreaker belongs inside ROW_NUMBER's OVER clause, not only in the final ORDER BY.

Concepts

SELECT Window Functions RANK ROW_NUMBER JOIN GROUP BY

Practise the topic: SQL practice questions · Window function practice · JOIN practice · GROUP BY exercises · Ranking function practice

Read the concept: ROW_NUMBER vs RANK vs DENSE_RANK

In these company practice sets

Capital One · Stripe · 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.

Busiest Merchants, and What a Tie Does to the Rank

Schema

Hint

Concepts

In these company practice sets

Related questions