The Ad Ledger
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
- General Assembly
Problem
Closing the books on the second quarter of 2024, a finance analyst needs the total ad revenue booked in that quarter alone, drawn from ad_impressions.
Annual ad revenue. On the record.
Tables
ad_impressions
Columns: impression_id INTEGER, user_id INTEGER, ad_campaign TEXT, impression_time TEXT, clicked INTEGER, revenue REAL
| 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
| total_revenue |
|---|
| 8.25 |
Worked solution and explanation
What this problem is really testing
Under the finance costume, this is a SUM fenced to a single calendar quarter that sits in the INTERIOR of a table holding several years and all four quarters. Everyone writes SUM(revenue). What separates people is realizing that BOTH ends of the date range are load-bearing: the lower bound drops Q1 and the earlier years, and the upper bound drops Q3, Q4 and everything after. Set only the lower bound and you quietly fold later periods into this quarter, and the closed number ties out wrong.
Reading the shape of the data
ad_impressions is not one quarter of data. It spans several years and every quarter, and the period you are closing is Q2, which sits between Q1 below it and Q3 above it. So the target quarter is boxed in on both sides: earlier periods below it, later periods above it. That is exactly why a one-sided filter is not enough here.
Step 1: Fence the quarter on both ends
Use a half-open range: impression_time >= '2024-04-01' AND impression_time < '2024-07-01'. The lower bound excludes Q1 and every earlier year, the upper bound excludes Q3, Q4 and everything after. Half-open (< the start of July) avoids any off-by-one on the June 30 boundary, and unlike strftime(...) it stays sargable against an index on impression_time.
Step 2: Let SUM handle the nulls
SUM(revenue) over the filtered rows is the whole computation. SUM ignores NULL revenue for free, so the many impressions that never earned money neither error nor count as zero. Do not wrap it in COALESCE to force zeros: that changes nothing for the total and only muddies intent.
Step 3: Return one clean scalar
Alias the scalar as total_revenue so the single-row, single-column output reads for itself.
The solution
SELECT SUM(revenue) AS total_revenue
FROM ad_impressions
WHERE impression_time >= '2024-04-01'
AND impression_time < '2024-07-01'Common follow-up questions
- Now give me revenue per month across that quarter instead of one total. (Tests adding `GROUP BY` with date truncation while keeping the range filter.)
- If a quarter had no earning impressions at all, what would this query return, and when would you `COALESCE` it to 0? (Probes whether they know `SUM` ignores nulls but display sometimes wants a zero.)
- This table is 200M rows across many years. How would you keep this quarterly total cheap if finance ran it every day? (Probes partition pruning and pre-aggregated rollups at larger scale.)