The Open Question

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: L4
Asked in: Meta

Problem

Dates on which not a single one of our push notifications was opened should not appear at all. For every other send date in push_notifs, report the open rate: the share of that day's notifications that were opened, where every row counts as a send even if it failed or its open flag is blank.

Push sent. How many opened?

Tables

push_notifs

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

push_notifs: 10 of 200 rows
notif_id	user_id	title	platform	status	sent_at	opened	campaign
1053	100	New message from team	android	pending	2026-02-02 01:07:00	0	winback_feb
1106	197	Weekly digest ready	web	failed	2026-03-03 02:14:00	0	promo_summer
1159	294	Price drop alert	iOS	bounced	2026-04-04 03:21:00	1	onboard_v2
1212	391	Security alert	Android	Delivered	2026-05-05 04:28:00	0	NULL
1265	488	Payment received	WEB	FAILED	2026-06-06 05:35:00	NULL	loyalty_2024
1318	585	Complete your profile	basic	delivered	2026-07-07 06:42:00	1	NULL
1371	682	New feature available	premium	pending	2026-08-08 07:49:00	0	reengagement
1424	779	Subscription renewal	ios	failed	2026-09-09 08:56:00	0	NULL
1477	876	Flash sale: 50% off	android	bounced	2026-01-10 09:03:00	1	flash_sale
1530	973	Your order shipped!	web	Delivered	2026-02-11 10:10:00	NULL	retention_q1

Expected output

First 5 of 56 rows
send_date	open_rate
2022-03-24	1
2022-06-07	1
2022-06-11	1
2026-01-17	0.5
2026-01-28	0.5

Worked solution and explanation

What this really is

This is a post-aggregation filter wearing a metrics-dashboard costume. Anyone can average opened per day; the thing that separates candidates is the exclusion rule: keep only the days that saw at least one open. That test runs on an aggregate (SUM(opened)), not on a single row, so it has to live in HAVING, after the grouping. Reach for WHERE instead and you strip rows before they are grouped: a day with one open and nine misses would keep only the opened row and report a 100% rate. Same keyword confusion, silently corrupted daily numbers.

Break down the requirements

Step 1: Normalize `sent_at` to a send date

DATE(sent_at) collapses each timestamp to its calendar send date, so everything sent on the same day lands in one bucket. sent_at is stored as text here, and DATE() reads the date portion cleanly.

Step 2: Group by date and compute the rate

GROUP BY DATE(sent_at) with SUM(opened) * 1.0 / COUNT(*) is opens over total sent for the day. The * 1.0 forces real division; without it, integer math floors 1/2 to 0 and every rate collapses to 0.

Step 3: Keep only days with at least one open

HAVING SUM(opened) > 0 drops days where nothing was opened. Because it tests a per-day aggregate, it can only run after the grouping, which is exactly the job HAVING exists for. A day whose opens are all null also fails this test, since SUM over only nulls is null and null is not greater than 0.

The solution

Daily open rate with a post-aggregation filter

SELECT
    DATE(sent_at) AS send_date,
    SUM(opened) * 1.0 / COUNT(*) AS open_rate
FROM push_notifs
GROUP BY DATE(sent_at)
HAVING SUM(opened) > 0

Common follow-up questions

If `opened` is null for some notifications, how does that change the numerator versus the denominator of your open rate? (Probes understanding of how NULL propagates through SUM and COUNT on the `opened` column.)
`push_notifs.user_id` has roughly 6,000,000 distinct values. What index strategy would you use to avoid a full scan when the report is scoped to one user? (Tests indexing knowledge specific to the high-cardinality `user_id` column in `push_notifs`.)
If the at-least-one-open rule became keep only days above the median daily open rate, how would you restructure the query? (Tests the ability to replace a static HAVING threshold with a dynamic, subquery-based one.)