# The Price of a Tap > The campaign fired thousands of pushes. Find what each open really cost. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Marketing has been running promotional push campaigns whose names all contain 'promo', and wants to know how hard each one worked to earn a customer. For every promo campaign, broken out by year, find how many notifications it took to earn a single open: total notifications sent divided by the number that were opened. ## 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 rows divided by total opens, per campaign per year. Anyone can write a COUNT and a SUM. What separates candidates is noticing the denominator can be zero: a promo campaign can send a thousand pushes and earn zero opens, and the moment you divide by that you either crash or emit a garbage row. The real skill being probed is computing count over sum per group while guarding the denominator, plus doing the pattern match and the year bucketing without conflating groups. --- ### The trap: a zero denominator SUM(opened) is your acquisition count. For any (campaign, year) where nobody opened, that sum is 0. Divide COUNT(*) by it and SQLite hands back either an error or a NULL depending on types, and your result set quietly gains a meaningless row. 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: Match promo campaigns WHERE campaign LIKE '%promo%' keeps any name containing the substring. NULL campaigns fail the LIKE and drop out for free, which is what you want. Pair it with opened IS NOT NULL so rows with a missing flag never distort either count. #### Step 2: Bucket by year strftime('%Y', sent_at) turns each timestamp into its four-digit year as text. Group by campaign and that same year expression together so 2025 and 2026 traffic for one campaign stay 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 group that would divide by zero. --- ### The solution **Cost per acquisition, per promo campaign per year** ```sql SELECT campaign, strftime('%Y', sent_at) AS year, COUNT(*) * 1.0 / SUM(opened) AS cost_per_acquisition FROM push_notifs WHERE campaign LIKE '%promo%' AND opened IS NOT NULL GROUP BY campaign, strftime('%Y', sent_at) HAVING SUM(opened) > 0 ``` **Naive: no denominator guard** Drop the HAVING clause and every promo campaign with zero opens either errors out or returns a nonsense ratio. On real send data the zero-open campaigns are common (a bad list, a dead segment), so this is not a rare edge case. **Correct: HAVING guard** HAVING SUM(opened) > 0 removes those groups after aggregation. The output only contains campaigns that actually earned at least one open, which is the only case where cost per acquisition is even defined. > **Common Pitfall** > > A subtle miss is using COUNT(opened) instead of SUM(opened) for the denominator. COUNT(opened) counts rows where opened is non-null, including the zeros, so you would be dividing by total delivered rather than total opened. The acquisition count is the SUM of the 0/1 flag, not its COUNT. > **Interviewers Watch For** > > Interviewers watch whether you reach for HAVING unprompted. Guarding the denominator without being told the data contains zero-open campaigns signals you think about failure modes, not just the happy path. Forcing real division with * 1.0 (or a CAST) to dodge integer truncation is a second tell. > **Cost Analysis** > > On the full 80,000,000-row table the LIKE '%promo%' predicate has a leading wildcard, so it cannot use a b-tree index and becomes a scan. If this ran often you would precompute an is_promo boolean column or a per-(campaign, year) rollup, turning the query into a cheap lookup instead of a full aggregation each time. --- ## Common follow-up questions - What does your query return if every value in push_notifs.campaign is NULL, and why? _(Tests whether the candidate anticipates the empty-result and NULL-handling edge cases.)_ - The LIKE '%promo%' filter cannot use an index. How would you restructure the data or the query to avoid scanning all 80,000,000 rows on every run? _(Tests indexing knowledge specific to the high-cardinality sent_at column and the leading-wildcard filter.)_ - 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_price_of_a_tap) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.