# Peak Ad Revenue Moment

> The single peak earning moment.

Canonical URL: <https://datadriven.io/problems/peak_ad_revenue_moment>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The ad ops team is investigating a revenue outlier. Find the single highest-revenue ad impression and show when it occurred alongside the revenue amount.

## Worked solution and explanation

### Why this problem exists in real interviews

The ad_impressions table contains ad_campaign and impression_time values that must be processed with top-N selection. This appears as a fundamentals check to probe whether you reason about the correct aggregation grain before writing any window or GROUP BY clause.

---

### Break down the requirements

#### Step 1: Order by revenue descending

`ORDER BY revenue DESC LIMIT 1` finds the single highest-revenue impression.

#### Step 2: Return timestamp and revenue

`SELECT impression_time, revenue` shows when the peak occurred and how much it generated.

---

### The solution

**ORDER BY DESC LIMIT 1 for top row**

```sql
SELECT impression_time, revenue
FROM ad_impressions
ORDER BY revenue DESC
LIMIT 1
```

> **Cost Analysis**
>
> At `ad_impressions` (200,000,000 rows), a full table scan is expensive. Partition pruning (if the table is partitioned on the filter column) is the first optimization. A covering index on the `GROUP BY` + filter columns eliminates random I/O. Consider a materialized view for repeated dashboard queries.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- If ad_impressions.impression_id could contain unexpected NULL values, how would your query behave? _(Tests NULL awareness even when the schema does not currently allow NULLs in impression_id.)_
- How would you verify that your aggregation on ad_impressions.impression_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- With millions of distinct values in ad_impressions.impression_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like impression_id.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/peak_ad_revenue_moment)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.