The Weekly Pulse

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

Zenefits

Problem

How many ios, android and web pushes in push_notifs went to untreated users on each day of the week (0 for Sunday through 6 for Saturday), where untreated means sitting in a control or holdout group of at least one experiment in experiments, and a platform spelled with different capitalization is still the same platform? For the growth team's baseline, each weekday also reports how many unique users got at least one push on those three platforms, so a user whose only pushes that day went to any other platform value is not counted.

Notifications by platform and day. When does the audience actually show up?

Tables

push_notifs

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

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

Expected output

Worked solution and explanation

The trap beneath the pivot

This is a per-platform pivot of notification volume with a unique-user reach count beside it, scoped to the untreated cohort, and three quiet mistakes decide it. The cohort is 'users in a control or holdout arm of some experiment', and the instinct is to JOIN push_notifs to experiments to enforce it. That JOIN fans out any user enrolled in more than one experiment, so each of their notifications is counted once per enrollment and every column inflates. A semi-join (WHERE user_id IN (...)) tests membership without duplicating rows. Second, the platform text is stored dirty ('iOS', 'WEB', 'Android'), so a plain platform = 'ios' drops every capitalized row and undercounts all three columns; fold with LOWER() first. Third, reach is not volume: COUNT(*) counts notifications, but a user who received three still reaches once, so the unique-users column needs COUNT(DISTINCT pn.user_id).

Working through it

Step 1: Scope to the untreated cohort with a semi-join

The cohort is users placed in a control or holdout arm of at least one experiment. Express membership as a semi-join: WHERE pn.user_id IN (SELECT user_id FROM experiments WHERE variant IN ('control', 'holdout')). The inner filter narrows experiments to the untreated arms, and IN tests membership once per notification, so a user enrolled in several experiments never has their rows duplicated the way a JOIN would.

Step 2: Turn the timestamp into an integer weekday

Derive the weekday with strftime('%w', pn.sent_at), which yields '0' for Sunday through '6' for Saturday. Wrap it in CAST(... AS INTEGER) in the SELECT so day_of_week comes back as a real integer 0..6 rather than the text '0'..'6' the function returns, matching the required output type.

Step 3: Fold the dirty platform text and drop the junk

Fold the platform text before comparing: LOWER(pn.platform) collapses 'iOS'/'ios', 'WEB'/'web' and 'Android'/'android' onto one spelling. Restrict to the real platforms with AND LOWER(pn.platform) IN ('ios', 'android', 'web') so junk values like 'basic' and 'premium' are dropped, which also keeps the unique-user count honest.

Step 4: Pivot volume into three platform columns

Pivot platform into three columns with SUM(CASE WHEN LOWER(pn.platform) = 'ios' THEN 1 ELSE 0 END) and its siblings, so each bucket is an unambiguous notification count. GROUP BY strftime('%w', pn.sent_at) collapses to one row per weekday.

Step 5: Count unique users reached, not notifications

Add the reach column with COUNT(DISTINCT pn.user_id). Reach asks how many people were touched, not how many pushes went out, so a user who received three notifications in a weekday must count once. A plain COUNT(*) here would silently report notification volume under a column labeled unique users.

The solution

SELECT
    CAST(strftime('%w', pn.sent_at) AS INTEGER) AS day_of_week,
    SUM(CASE WHEN LOWER(pn.platform) = 'ios'     THEN 1 ELSE 0 END) AS ios_count,
    SUM(CASE WHEN LOWER(pn.platform) = 'android' THEN 1 ELSE 0 END) AS android_count,
    SUM(CASE WHEN LOWER(pn.platform) = 'web'     THEN 1 ELSE 0 END) AS web_count,
    COUNT(DISTINCT pn.user_id) AS users_reached
FROM push_notifs pn
WHERE pn.user_id IN (
        SELECT user_id FROM experiments WHERE variant IN ('control', 'holdout')
    )
    AND LOWER(pn.platform) IN ('ios', 'android', 'web')
GROUP BY strftime('%w', pn.sent_at)

Common follow-up questions

• How would the day counts change if you swapped the `IN` semi-join for a `JOIN experiments ON pn.user_id = experiments.user_id`? (Tests whether the candidate recognizes that a JOIN duplicates a user's rows once per experiment enrollment.)
• What happens to your three columns if a new platform like 'desktop' starts appearing in `platform`? (Tests whether the candidate sees that hard-coded CASE buckets silently ignore new platform values.)
• With millions of distinct `user_id` values, what index would keep the `WHERE user_id IN (...)` probe fast? (Tests indexing knowledge for the membership probe on a high-cardinality key.)