The Notification Lifecycle

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

Google

Problem

Our push notification system records a delivery status for every message, but that status is entered inconsistently, so the same outcome shows up in different letter cases. For each registered user, count how many of their notifications were delivered, how many were opened, and how many failed, where an open is captured by its own flag rather than the status text.

Sent, opened, ignored. What happened after the alert went out?

Tables

push_notifs

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this is really about

Under the notification-dashboard costume, this is a conditional pivot: collapse many notification rows per user into one row with three counts. Everyone can write the CASE sums. The two things that actually separate candidates are hiding in the data: the status text is logged in mixed casing, and 'opened' is not a status at all. Match status case-sensitively and you silently undercount every 'Delivered' and 'FAILED'; treat opened as a status value and the column comes back all zeros because no such status exists.

The two traps

Naive (both traps)

SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) and SUM(CASE WHEN status = 'opened' THEN 1 ELSE 0 END). Delivered undercounts on mixed casing; opened is always 0.

Correct

SUM(CASE WHEN LOWER(status) = 'delivered' THEN 1 ELSE 0 END) and SUM(CASE WHEN opened = 1 THEN 1 ELSE 0 END). Casing normalized, opens read from the real signal.

Building it

Step 1: Restrict to registered users

Join push_notifs to users on user_id. The inner join is the 'registered users only' filter: any notification whose user_id has no matching row is dropped, and users with no notifications never appear.

Step 2: One row per user

GROUP BY user_id collapses each user's many notification rows into a single output row. The three CASE sums are evaluated within each group.

Step 3: Pivot the three metrics

delivered_count and failed_count come from LOWER(status) so casing cannot split a count; opened_count comes from opened = 1 so a null flag counts as not opened. Three independent conditional sums, one pass over the group.

The solution

SELECT
    pn.user_id,
    SUM(CASE WHEN LOWER(pn.status) = 'delivered' THEN 1 ELSE 0 END) AS delivered_count,
    SUM(CASE WHEN pn.opened = 1 THEN 1 ELSE 0 END) AS opened_count,
    SUM(CASE WHEN LOWER(pn.status) = 'failed' THEN 1 ELSE 0 END) AS failed_count
FROM push_notifs pn
JOIN users u ON pn.user_id = u.user_id
GROUP BY pn.user_id

Common follow-up questions

• Add an open rate per user: opened over delivered. How do you avoid dividing by zero for users with no deliveries? (Tests whether they can extend the pivot to a derived open rate and handle division safely.)
• Now include registered users who received no notifications at all, showing zeros. What changes in the join? (Probes left join versus inner join semantics and COALESCE for zero-fill.)
• This dashboard refreshes hourly on an append-only table partitioned by `sent_at`. How would you maintain the counts incrementally instead of re-scanning history? (Tests incremental aggregation design against a partitioned, append-only table.)