SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…