Campaign Cost Effectiveness

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Uber

Problem

An analyst closing the books on 2024 and 2025 wants to see which campaigns in ad_impressions gained ground across those two full years. For each campaign, report its total revenue over the two years and the fraction of its impressions served in 2025, as a decimal between 0 and 1.

Money in, conversions out. What is the ratio?

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

What this problem really is

This is a conditional share dressed up as a campaign momentum report. The total revenue is the easy half: SUM(revenue) per ad_campaign and you are done. The real question is the fraction of each campaign's two-year impressions that landed in 2025, and it hides two traps. Count the year as it comes and you count every impression as a later-year impression, so every share is 1. Divide two whole-number counts and nearly every share truncates to 0. Either way the report says nothing about which campaigns gained ground.

Building it

Step 1: Fence the two full years

strftime('%Y', impression_time) reads the year as text like '2025', and the quoted bounds are text too, so BETWEEN '2024' AND '2025' compares like with like. It is inclusive at both ends, which keeps both full years and leaves the partial 2026 out.

Step 2: Count the later year, then the whole

Inside the window a row is either 2024 or 2025. Nulling out the 2024 year leaves COUNT with only the 2025 rows, while COUNT(*) is the campaign's full two-year population. Same rows, two populations, one pass.

Step 3: Divide in decimal, one row per campaign

Both counts are integers, so CAST(... AS REAL) on the numerator keeps the division fractional. SUM(revenue) skips the empty revenue values on its own, and GROUP BY ad_campaign gives one row per campaign.

SELECT
    ad_campaign,
    SUM(revenue) AS total_revenue,
    CAST(COUNT(NULLIF(strftime('%Y', impression_time), '2024')) AS REAL) / COUNT(*) AS recent_share
FROM ad_impressions
WHERE strftime('%Y', impression_time) BETWEEN '2024' AND '2025'
GROUP BY ad_campaign

Whole-number division

COUNT(...) / COUNT(*) divides integer by integer. A campaign with 5 of 9 impressions in 2025 reports 0, and only a campaign with every impression in the later year reports 1.

Decimal division

CAST(COUNT(...) AS REAL) / COUNT(*) keeps the fraction. The same campaign reports 0.556, and the column finally separates campaigns that leaned into 2025 from those that faded.

Common follow-up questions

• Marketing now wants the later-year share of revenue, not of impressions. What changes? (Tests moving the condition onto a summed measure instead of a count.)
• How would you flag campaigns whose share is above 0.5 in the same query? (Tests filtering on an aggregate after `GROUP BY`.)
• What breaks if the window slides to the last 24 months instead of two calendar years? (Tests replacing the text-year fence with a date range on `impression_time`.)
• How would you keep the time filter while still letting the engine skip old partitions? (Tests writing a sargable range on `impression_time` instead of a function of it.)