Interview Drills · ROW_NUMBER() · deterministic tie-breaker
40. Largest charge per account
Concept: ROW_NUMBER() · deterministic tie-breakershow
For exactly one row per group, number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY ...) and keep row 1. If two rows tie on the sort, Postgres may pick either one, so add a tie-breaker column to the window's ORDER BY to make the answer repeatable.
Example (different data):
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY store_id ORDER BY total DESC, receipt_id DESC) AS rn
FROM receipts
) AS r
WHERE rn = 1;Your task
The fraud team reviews each account's single largest card charge; when two charges tie, they want the later-recorded one (higher id), since it's the likely duplicate.
Return account_id, id, merchant, amount and txn_date of each account's largest transaction, exactly one row per account. If amounts tie, keep the one with the higher id. Sort by account_id.
Starting Postgres in your browser…
Checking your access…