Drill 5 of 12 in Interview Classics
0

Interview Classics · Scalar subquery · MAX below the MAX

5. Second-largest charge

Concept: Scalar subquery · MAX below the MAXshow

An aggregate with no GROUP BY always returns exactly one row, with NULL when nothing matches, while LIMIT/OFFSET returns zero rows when there's nothing at that position. So the classic 'second-highest, or NULL if there isn't one' is the MAX of everything below the MAX: a scalar subquery finds the top value, the outer MAX finds the next distinct one.

Example (different data):

SELECT MIN(lap_time) AS second_fastest
FROM laps
WHERE lap_time > (SELECT MIN(lap_time) FROM laps);

Your task

Classic interview question: a fraud dashboard tile shows the runner-up card charge, and it must show NULL rather than vanish when there isn't one.

Return one row with one column, second_largest_amount: the second-highest distinct amount in transactions (charges that tie count once). Write it so it would return NULL, not zero rows, if every charge had the same amount.

Starting Postgres in your browser…

Checking your access…