# The Ad Ledger

> Annual ad revenue. On the record.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The finance team needs the total ad impression revenue for 2026 to close out the annual report. Return a single total.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a straightforward aggregation problem that tests whether you can apply a date filter and compute a single scalar total. Interviewers use it as a warm-up to verify you understand `SUM`, `WHERE` with date extraction, and that you return a single row with a clear alias.

---

### Break down the requirements

#### Step 1: Filter to the target year

Use `WHERE impression_time >= '2026-01-01' AND impression_time < '2027-01-01'` (or `strftime('%Y', impression_time) = '2026'`) to isolate the year's rows from the 200M-row table.

#### Step 2: Sum the revenue

`SUM(revenue)` across the filtered rows produces the single total the finance team needs.

#### Step 3: Return a clean alias

Alias the result as `total_revenue` so the output is self-documenting.

---

### The solution

**Date-filtered aggregation for annual total**

```sql
SELECT SUM(revenue) AS total_revenue
FROM ad_impressions
WHERE impression_time >= '2026-01-01'
  AND impression_time < '2027-01-01'
```

> **Cost Analysis**
>
> If `impression_time` is the partition key (365 partitions), the planner prunes to ~365 partitions for one year. Without partition pruning, this is a full 200M-row scan. A range filter is faster than `strftime` because it avoids per-row function evaluation and can use an index.

> **Interviewers Watch For**
>
> Interviewers watch whether you use a range filter (`>=` and `<`) rather than `strftime('%Y', ...)`. The range approach is sargable and index-friendly; the function-based approach forces a full scan.

> **Common Pitfall**
>
> Using `YEAR(impression_time) = 2026` or `strftime` prevents the optimizer from using an index on `impression_time`. Always prefer range predicates for date filtering.

---

## Common follow-up questions

- What if the prompt asked for monthly totals instead of an annual total? _(Tests adding GROUP BY with date truncation.)_
- How would you handle NULL revenue values? _(Tests awareness that SUM ignores NULLs but COALESCE may be needed for display.)_
- What if this table had 10B rows spanning 5 years? _(Probes partition pruning and pre-aggregated materialized view strategies.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_ad_ledger)
- [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.