Seen or Ignored
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
Every send our push notification system makes lands in push_notifs, along with whether the recipient opened it. The engagement team wants each platform's fraction of sent notifications that were opened, strongest open rate first.
A send is only half the story. Find where the taps actually land.
Tables
push_notifs
Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT
| notif_id | user_id | title | platform | status | sent_at | opened | campaign |
|---|---|---|---|---|---|---|---|
| 1053 | 100 | New message from team | android | pending | 2026-02-02 01:07:00 | 0 | winback_feb |
| 1106 | 197 | Weekly digest ready | web | failed | 2026-03-03 02:14:00 | 0 | promo_summer |
| 1159 | 294 | Price drop alert | iOS | bounced | 2026-04-04 03:21:00 | 1 | onboard_v2 |
| 1212 | 391 | Security alert | Android | Delivered | 2026-05-05 04:28:00 | 0 | NULL |
| 1265 | 488 | Payment received | WEB | FAILED | 2026-06-06 05:35:00 | NULL | loyalty_2024 |
| 1318 | 585 | Complete your profile | basic | delivered | 2026-07-07 06:42:00 | 1 | NULL |
| 1371 | 682 | New feature available | premium | pending | 2026-08-08 07:49:00 | 0 | reengagement |
| 1424 | 779 | Subscription renewal | ios | failed | 2026-09-09 08:56:00 | 0 | NULL |
| 1477 | 876 | Flash sale: 50% off | android | bounced | 2026-01-10 09:03:00 | 1 | flash_sale |
| 1530 | 973 | Your order shipped! | web | Delivered | 2026-02-11 10:10:00 | NULL | retention_q1 |
Expected output
| platform | open_ratio |
|---|---|
| ios | 0.375 |
| iOS | 0.3076923076923077 |
| basic | 0.2916666666666667 |
| premium | 0.25 |
| web | 0.23076923076923078 |
Worked solution and explanation
What this really tests
This is a denominator-definition problem wearing a push-notification costume. The metric looks trivial: opens over sends, split by platform. The question the interviewer is really probing: when some sends never report back an open signal (opened is NULL), do those rows belong in your denominator? The ask is the fraction of ALL sent notifications, so they do. Candidates who miss this quietly divide by the wrong number, and every platform looks better than it is.
Building it
Step 1: Count the confirmed opens
opened is a 0/1 flag, so SUM(opened) is the count of confirmed opens. A NULL contributes nothing to it, which is exactly right: an untracked send was not confirmed opened.
Step 2: Divide by every send
Divide by COUNT(*), not COUNT(opened). COUNT(*) counts every send for the platform, including the NULL-opened rows, which gives the fraction of all sent notifications. The * 1.0 forces real division, so the result is a decimal instead of a truncated integer 0.
Step 3: Split by platform and sort
Break the calculation out per platform value as stored, and order by open_ratio descending so the strongest platforms surface first. Several platforms land on exactly the same ratio on this data, and the ask says nothing about them, so tied rows may come out in either order. Do not invent a tiebreaker.
The solution
SELECT platform,
SUM(opened) * 1.0 / COUNT(*) AS open_ratio
FROM push_notifs
GROUP BY platform
ORDER BY open_ratio DESC- `AVG(opened)` (wrong)
- Divides by
COUNT(opened), which drops every NULL-opened send. Platforms with many untracked sends get an inflated open rate that has no relationship to how many notifications you actually pushed. - `SUM(opened) * 1.0 / COUNT(*)` (right)
- Divides by
COUNT(*), so untracked sends stay in the denominator and count as not-opened. This is the true fraction of everything you sent.
Common follow-up questions
- How would you change this to the open rate among only the notifications that were actually delivered? (Tests whether the candidate can separate delivery from engagement and move the denominator to match, including the mixed-case `status` values.)
- How would you report each platform's open rate week over week to spot a drop after a release? (Tests comfort with time-bucketed metrics on `sent_at`.)
- A brand-new platform shows a 100 percent open rate from three sends. How would you keep low-volume platforms from topping the list? (Tests awareness of small-sample noise in ratio metrics.)