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

Each row of push_notifs is one push notification, recording when it was sent and whether it was opened. Find how many notifications were opened in each month of the year, 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

Take away the engagement story and this is a tally of a three-valued flag into month buckets you manufacture from a text timestamp. Anyone writes COUNT(*). What separates candidates is the grain and the filter. The bucket is the month of the year, pooled across every year the table spans. Only rows with opened = 1 are opens. If you group on the raw sent_at, you get one row per send. If you group by year and month, January splits five ways. If you count every row, each month absorbs its unopened and unflagged sends. Then comes the order: busiest first means sorting by opened_count, not by the calendar.

The trap

Building it

Step 1: Manufacture the month bucket

Derive the month with CAST(strftime('%m', sent_at) AS INTEGER). Casting here, once, means the SELECT, the GROUP BY and the ORDER BY all see a clean integer from 1 to 12.

Step 2: Keep only the opens

WHERE opened = 1 drops the zeros and the NULL flags before anything is counted. That way COUNT(*) counts confirmed opens, and a month with no opens never becomes a row.

Step 3: Tally and sort by volume

GROUP BY month collapses each calendar month into one row, COUNT(*) tallies its opens, and ORDER BY opened_count DESC puts the busiest month on top. Months 1, 4 and 7 tie at 9 opens. The question only asks for busiest first, so their order among themselves is free.

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

Filter, then count

WHERE opened = 1 followed by COUNT(*) states the intent where a reviewer can see it. NULLs and zeros are excluded by the predicate, and the count runs only over rows you have already vouched for.

Sum the flag

SUM(opened) over the whole table assumes the flag only ever holds 0 or 1, and it hides the filter inside the aggregate. It also keeps months 2, 10 and 11 as zero rows, which is three rows the answer does not contain.

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 adds the year as a second grouping key, and whether they notice the grain change.)
• If a chunk of rows has `opened` set to NULL because delivery never confirmed, how does that change your count, and why does `SUM(opened)` return more rows than your query? (Tests NULL semantics in comparisons and aggregates, and whether the candidate notices the zero-open months.)
• 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.)