Push Opens by Platform and Campaign

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

Every notification sent is a row in push_notifs, carrying the platform it went to and an opened flag. Find how many unique users opened a notification on each platform, listing the platform with the most openers first.

Opens by platform and campaign.

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

This is a count of people dressed up as a count of events. push_notifs holds one row per notification, so a user who opens two notifications on the same platform appears twice. The question asks how many people opened, not how many opens happened. Everyone writes the WHERE opened = 1 and the GROUP BY platform. What separates candidates is whether the aggregate counts users or rows. Write COUNT(*) and nearly every platform doubles on this data: 'ios' reads 9 instead of 5. The comparison the engagement team wanted then measures how often repeat openers come back, not how many users respond.

Walking to the query

Step 1: Keep only real opens

opened is 1, 0 or NULL. A NULL flag means no open was ever recorded, so it is not an open. opened = 1 drops the zeros and the NULLs in one go, because NULL = 1 is never true. That is exactly the behavior you want, so no extra guard is needed.

Step 2: Group on platform as it is stored

The column carries 'ios' and 'iOS' as separate labels, plus odd values like 'basic' and 'premium'. The expected output keeps each stored label on its own row, so the grain is the label in the table. Merging spellings would be a different answer.

Step 3: Count people, not rows

COUNT(DISTINCT user_id) collapses a user's repeat opens on one platform into a single opener. This is the line the whole problem turns on.

Step 4: Sort by the count you just named

ORDER BY unique_openers DESC reuses the alias. Four platforms tie at 3 and three at 4. The question only asks for most openers first, so tied platforms may come in any order.

SELECT platform, COUNT(DISTINCT user_id) AS unique_openers
FROM push_notifs
WHERE opened = 1
GROUP BY platform
ORDER BY unique_openers DESC

Counting rows

COUNT(*) returns 'ios' 9, 'iOS' 8, 'android' 8, 'basic' 7. Each opener's second row is counted again, so the numbers reward repeat behavior.

Counting users

COUNT(DISTINCT user_id) returns 'ios' 5, then 'android', 'basic' and 'iOS' level at 4. One user is one opener, which is what 'how many users' means.

Common follow-up questions

• Now report each platform's open rate: unique openers over unique recipients. What goes in the denominator? (Tests whether the candidate keeps both sides at the user grain.)
• If 'ios' and 'iOS' were merged, why is the result not 5 + 4? (Distinct counts are not additive across groups.)
• Break it down by `campaign` as well. Where do notifications with a NULL `campaign` end up? (NULL keys form their own group.)