The Heaviest Hitters

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Apple

Problem

The ad ops team is auditing which impressions pulled in the most money. Surface the three highest-revenue impressions, each with when it occurred and the revenue it earned, biggest earner first and the earlier impression ahead whenever two tie on revenue.

A handful of impressions earn more than the rest combined. Bring them to the top.

Tables

ad_impressions

Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL

Expected output

Worked solution and explanation

What this problem is really testing

This is a row-level top-N with a tie at the cut, dressed up as a revenue audit. Everyone gets the sort and the cap right. What separates candidates is the cut itself: five impressions in ad_impressions share the top revenue of 4.5 and only three fit. If revenue is the only sort key, the engine decides which three survive, and two queries that look correct return different audits. The question settles it (the earlier impression wins), so the query has to carry impression_time after revenue DESC. The other way to lose is to hear 'highest' and reach for MAX(revenue) or a GROUP BY revenue. Both fold those five impressions into one value and throw away the timestamps the audit asked for.

The trap

There is no grouping here. Every row is already one impression, so there is nothing to collapse. Numbering rows with ROW_NUMBER() and filtering to three also works, but it is more machinery than the problem needs and it reads worse. The quieter subtlety is NULL: 101 of the 200 impressions have no revenue. The grader's engine sorts NULLs last under ORDER BY revenue DESC, so they never reach the top three. On Postgres, where a descending sort puts NULLs first, you would add NULLS LAST.

Step 1: Sort by revenue, largest first

The audit reads best-first, so revenue DESC is the primary key. That alone puts all five 4.5 impressions at the top, in no guaranteed order among themselves.

Step 2: Break the tie on impression_time

The five tied impressions are dated each December from 2021 to 2025. Sorting impression_time ascending after revenue DESC puts the earliest first, so the cut always lands on the 2021, 2022 and 2023 rows, whatever the engine and however many times it runs.

Step 3: Cap at three and keep two columns

LIMIT 3 turns the sort into a top-N. Select only impression_time and revenue, the two things the audit reads.

The solution

SELECT impression_time, revenue
FROM ad_impressions
ORDER BY revenue DESC, impression_time
LIMIT 3

Common follow-up questions

• If the audit wanted the most recent impression to win a revenue tie, what changes, and which rows come back? (Tests that the tie key is read off the question, not left to the engine.)
• How does your query treat impressions whose `revenue` is NULL, and would it behave the same on Postgres? (Tests where NULLs land in a descending sort on different engines.)
• At hundreds of millions of impressions, what index lets this query skip a full sort? (Tests bounded top-N planning on a large table.)