The Price of a Tap

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

Uber

Problem

For every named push campaign, broken out by the year it ran, marketing needs to know how hard it worked to earn a customer: how many notifications it took to win a single open. Count only the notifications that actually went out, not the ones still sitting in the queue as pending.

The campaign fired thousands of pushes. Find what each open really cost.

Tables

push_notifs

Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT

Expected output

Worked solution and explanation

What this problem is really testing

Strip off the marketing language and this is a ratio of two aggregates measured over the same group: total notifications sent divided by total opens, per campaign per year. Anyone can write a COUNT and a SUM. What separates candidates is scoping the population and guarding the denominator. The open count is SUM(opened), and for a campaign whose outcomes were never recorded that sum comes back NULL, not a number. Divide by it and you emit a garbage row instead of a cost. The real skill being probed is computing count over sum per group while keeping only named campaigns and notifications that actually left the queue, then dropping any group with no recorded open.

The trap: a denominator that isn't there

SUM(opened) is your acquisition count. For a campaign and year where nobody opened, or where every outcome is still unrecorded, that sum is 0 or NULL. Divide COUNT(*) by it and you get a divide-by-zero or a NULL cost that quietly joins the result set. The fix is a HAVING SUM(opened) > 0 filter that runs after grouping, so those groups never reach the division. A WHERE clause cannot do this: the group sum does not exist until the group is formed.

Break down the requirements

Step 1: Scope to named campaigns that actually went out

WHERE campaign IS NOT NULL drops the anonymous bucket that a GROUP BY would otherwise build for rows with no campaign. Pair it with status NOT LIKE 'pend%' so notifications still queued as 'pending', which never reached anyone, stay out of the sent count. NOT LIKE also rides over the messy casing in status ('Delivered', 'delivered', 'FAILED'), which a case-sensitive equality test would trip on.

Step 2: Bucket by year

strftime('%Y', sent_at) turns each sent_at timestamp into its four-digit year as text. Group by campaign and that same year expression together so a campaign that ran in both 2025 and 2026 stays two separate rows.

Step 3: Divide, then guard the denominator

COUNT(*) * 1.0 / SUM(opened) is notifications per open. The * 1.0 forces real division instead of integer truncation. HAVING SUM(opened) > 0 drops any campaign-year that earned no recorded open, the only groups where the cost is undefined.

The solution

SELECT
    campaign,
    strftime('%Y', sent_at) AS year,
    COUNT(*) * 1.0 / SUM(opened) AS cost_per_open
FROM push_notifs
WHERE campaign IS NOT NULL
  AND status NOT LIKE 'pend%'
GROUP BY campaign, strftime('%Y', sent_at)
HAVING SUM(opened) > 0

Naive: no denominator guard

Drop the HAVING clause and every campaign-year with no recorded open returns a NULL or divide-by-zero cost. On real send data whole campaigns go out with their open outcomes never logged, so these rows are common, not a rare edge case.

Correct: HAVING guard

HAVING SUM(opened) > 0 removes those groups after aggregation, so the output only contains campaign-years that actually earned an open, the only case where cost per open is even defined.

Common follow-up questions

• What does your query return for a `campaign` whose every row has `opened` set to `NULL`, and why? (Tests whether the candidate connects the `HAVING SUM(opened) > 0` guard to the never-recorded case.)
• How would you restructure the data or the query to avoid re-aggregating all of `push_notifs` on every run? (Tests indexing and rollup knowledge for the high-cardinality `sent_at` column in `push_notifs`.)
• If late-arriving rows were inserted after your query ran, how would you design an incremental rollup instead of re-aggregating the whole table? (Tests understanding of incremental aggregation patterns.)