When They Opened

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

Meta

Problem

The marketing team wants a month-by-month read on push engagement across the year. For each month, find how many notifications were opened, busiest month first.

Push after push goes out. Some months, people actually read them.

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 really is

Strip the marketing costume off and this is a per-bucket tally of a boolean flag, where the bucket is a month you have to manufacture out of a timestamp. Anyone can write COUNT(*). The two things that actually separate candidates: pulling the month out of sent_at in a type that groups cleanly, and counting only the confirmed opens without letting a NULL flag sneak in. Miss either and the number is quietly wrong, which is the worst kind of wrong in a dashboard.

The trap

Building it

Step 1: Manufacture the month bucket

Derive the month with CAST(strftime('%m', sent_at) AS INTEGER). Doing the cast here means every later reference (the SELECT, the GROUP BY, the ORDER BY) sees a clean 1 through 12.

Step 2: Keep only the opens

WHERE opened = 1 restricts to confirmed opens before any counting happens, excluding both 0 and NULL flags.

Step 3: Tally and order

GROUP BY month collapses each month into one row, COUNT(*) tallies it, and ORDER BY opened_count DESC, month puts the busiest month on top with a deterministic tie-break so equal counts don't shuffle between runs.

SELECT CAST(strftime('%m', sent_at) AS INTEGER) AS month,
       COUNT(*) AS opened_count
FROM push_notifs
WHERE opened = 1
GROUP BY month
ORDER BY opened_count DESC, month

Filter then COUNT(*)

WHERE opened = 1 then COUNT(*): the intent is explicit, NULL and 0 are both excluded by the predicate, and the count is over rows you have already vouched for.

SUM(opened)

SUM(opened) over the whole table: leans on the flag being exactly 1 or 0, treats NULL as it happens to fall out, and mixes the filter into the aggregate where a reviewer can't see it. Same answer here, weaker habit.

Common follow-up questions

• The current query pools every year's January together. How would you report opened counts per month within each year instead? (Tests whether the candidate separates the month bucket from the year and knows GROUP BY needs both keys to avoid pooling January 2025 with January 2026.)
• If a chunk of rows has opened set to NULL because delivery never confirmed, how does that change your count, and would SUM(opened) give the same answer? (Tests NULL semantics in comparisons and aggregates.)
• This runs on a dashboard every few minutes over 100M rows. What would you pre-compute so it stops scanning the raw table? (Tests indexing and pre-aggregation strategy on a very large partitioned table.)