Campaign Bookend Engagement

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

General Assembly

Problem

Each row of ad_impressions is one impression stamped to the second, and every campaign's traffic runs from a first calendar day to a last one. Alphabetically by campaign, what percentage of each campaign's impressions fell on that first day and what percentage on that last day, with the fraction kept rather than cut to a whole number?

First impression versus last. The gap.

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

Why this problem exists in real interviews

This is a per-group boundary comparison dressed up as campaign pacing. Every row has to be measured against its own campaign's first and last calendar day, and neither the row nor a single GROUP BY can see both at once. Anyone can write MIN(impression_time). The trap is comparing against that raw timestamp: it matches one instant, not the whole day, so a campaign whose last day carries two impressions reports half its true share. The quieter trap is types: SUM and COUNT(*) are both integers, so the division silently truncates 5.88 to 5 unless the count is cast first.

Break down the requirements

Step 1: Fix each campaign's bookend days

First CTE campaign_dates: MIN(DATE(impression_time)) and MAX(DATE(impression_time)) grouped by ad_campaign. Truncating inside the aggregate is the whole point: the bookend is a calendar day, not the first second of traffic.

Step 2: Compute each denominator once

Second CTE campaign_counts: COUNT(*) grouped by ad_campaign. Rows with a missing user_id or revenue are still impressions, so count rows, not a column.

Step 3: Count both bookends in the same pass

Join the fact rows back to both CTEs and let SUM(CASE WHEN DATE(a.impression_time) = cd.first_day THEN 1 ELSE 0 END) count the first day while its twin counts the last day, in the same aggregate.

Step 4: Cast the count before dividing

100 * CAST(SUM(...) AS REAL) / cc.total_impressions. Both the sum and total_impressions are integers, so without the cast every share comes back as a whole number.

The solution

WITH campaign_dates AS (
  SELECT ad_campaign,
         MIN(DATE(impression_time)) AS first_day,
         MAX(DATE(impression_time)) AS last_day
  FROM ad_impressions
  GROUP BY ad_campaign
),
campaign_counts AS (
  SELECT ad_campaign, COUNT(*) AS total_impressions
  FROM ad_impressions
  GROUP BY ad_campaign
)
SELECT a.ad_campaign,
       100 * CAST(SUM(CASE WHEN DATE(a.impression_time) = cd.first_day THEN 1 ELSE 0 END) AS REAL)
         / cc.total_impressions AS first_day_pct,
       100 * CAST(SUM(CASE WHEN DATE(a.impression_time) = cd.last_day THEN 1 ELSE 0 END) AS REAL)
         / cc.total_impressions AS last_day_pct
FROM ad_impressions a
JOIN campaign_dates cd ON a.ad_campaign = cd.ad_campaign
JOIN campaign_counts cc ON a.ad_campaign = cc.ad_campaign
GROUP BY a.ad_campaign
ORDER BY a.ad_campaign

Common follow-up questions

Common follow-up questions

• How would you also return the click-through rate on the first and last day? (Tests whether you add `SUM(CASE WHEN clicked = 1 AND DATE(impression_time) = cd.first_day ...)` without a second scan.)
• Rewrite this with window functions instead of CTEs. (Probes `MIN(DATE(impression_time)) OVER (PARTITION BY ad_campaign)` plus `COUNT(*) OVER (PARTITION BY ad_campaign)` to avoid the joins entirely.)
• Only include campaigns that ran at least seven different days. (Forces a `HAVING COUNT(DISTINCT DATE(impression_time)) >= 7` filter, which also removes the single-day edge case.)
• What changes if `impression_time` is stored as UTC but campaigns are reported in local time zones? (Tests date-boundary thinking: convert with `AT TIME ZONE` before truncating to a day.)