Drill 7 of 12 in Window Functions
0

Window Functions · ROW_NUMBER() · deterministic tie-breaker

7. 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…