# The Heaviest Hitters > A handful of impressions earn more than the rest combined. Bring them to the top. Canonical URL: 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. ## Tables ### ad_impressions - impression_id INTEGER - user_id INTEGER - ad_campaign TEXT - impression_time TEXT - clicked INTEGER - revenue REAL Sample rows (10 of 200): | impression_id | user_id | ad_campaign | impression_time | clicked | revenue | | --- | --- | --- | --- | --- | --- | | 253 | 100 | BRAND_AWARENESS_Q1 | 2026-02-02 01:07:00 | 0 | NULL | | 306 | 197 | PRODUCT_LAUNCH_X | 2026-03-03 02:14:00 | 0 | NULL | | 359 | 294 | RETARGETING_CART | 2026-04-04 03:21:00 | 0 | NULL | | 412 | 391 | HOLIDAY_PROMO | 2026-05-05 04:28:00 | 0 | NULL | | 465 | 488 | NEW_USER_ACQU | 2026-06-06 05:35:00 | 1 | 0.2 | | 518 | NULL | LOYALTY_PROGRAM | 2026-07-07 06:42:00 | 0 | NULL | | 571 | 682 | FLASH_SALE_48H | 2026-08-08 07:49:00 | 0 | NULL | | 624 | 779 | SUMMER_SALE_2024 | 2026-09-09 08:56:00 | 0 | NULL | | 677 | 876 | BRAND_AWARENESS_Q1 | 2026-01-10 09:03:00 | 0 | NULL | | 730 | 973 | PRODUCT_LAUNCH_X | 2026-02-11 10:10:00 | 1 | 0.35 | ## Expected output First 3 of 3 rows: | impression_time | revenue | | --- | --- | | 2021-12-15 12:00:00 | 4.5 | | 2022-12-15 12:00:00 | 4.5 | | 2023-12-15 12:00:00 | 4.5 | ## 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 **Sort descending, break the tie, take three** ```sql SELECT impression_time, revenue FROM ad_impressions ORDER BY revenue DESC, impression_time LIMIT 3 ``` > **Top-N is a sort and a cap** > > A top-N over one measure is `ORDER BY` plus `LIMIT`. Leaving out the window function keeps the query readable, and it lets the planner keep a three-row buffer instead of numbering every row. > **A tie at the cut is a coin flip** > > Five rows share 4.5 and `LIMIT 3` keeps three. With only `ORDER BY revenue DESC`, which three survive depends on scan order, so a rerun or a different engine can return a different audit. The tie key comes from the question: here the earlier `impression_time` wins, so it sorts ascending. > **A three-row heap, not a full sort** > > At production scale (hundreds of millions of impressions) a top-3 never needs a full sort. The planner keeps a three-element heap while it scans, so memory stays flat. An index on `(revenue DESC, impression_time)` goes further: the engine walks the high end and stops after three rows. > **Naming the cut before writing the query** > > The senior move is to spot the tie before typing: five rows at the top, three slots, so which ones win? A candidate who asks that, or reads it off the preview, is reasoning about the data. One who ships `ORDER BY revenue DESC LIMIT 3` and stops has a query that passes on some engines and fails on others. Saying where NULLs land in a descending sort, unprompted, is the second signal. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_heaviest_hitters) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.