Seen and Unseen
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
- L4
- Asked in
- Airbnb
Problem
Users on one platform keep complaining that they never see their two-factor codes, and the support lead wants push_notifs_2fa to settle which platform it is. Give each platform's open rate as a percentage of all codes sent to it, highest first.
Every ping lands somewhere. Not every one gets read.
Tables
push_notifs_2fa
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 |
|---|---|---|---|---|---|---|---|
| 1001 | 101 | Your 2FA code | android | delivered | 2026-02-02 01:07:00 | 1 | login_feb |
| 1002 | 102 | Your 2FA code | android | delivered | 2026-02-03 02:10:00 | 1 | login_feb |
| 1003 | 103 | Your 2FA code | android | delivered | 2026-02-04 03:15:00 | 0 | login_feb |
| 1004 | 104 | Your 2FA code | ios | delivered | 2026-02-05 04:20:00 | 1 | login_feb |
| 1005 | 105 | Your 2FA code | ios | delivered | 2026-02-06 05:25:00 | 0 | login_feb |
| 1006 | 106 | Your 2FA code | web | delivered | 2026-02-07 06:30:00 | 1 | login_mar |
| 1007 | 107 | Your 2FA code | web | delivered | 2026-02-08 07:35:00 | 0 | login_mar |
| 1008 | 108 | Your 2FA code | web | delivered | 2026-02-09 08:40:00 | NULL | login_mar |
| 1009 | 109 | Your 2FA code | ios | failed | 2026-02-10 09:45:00 | 0 | NULL |
| 1010 | 110 | Your 2FA code | android | bounced | 2026-02-11 10:50:00 | 1 | login_mar |
Expected output
| platform | open_rate_pct |
|---|---|
| android | 75 |
| web | 33.333333333333336 |
| ios | 33.333333333333336 |
Worked solution and explanation
What this really is
Beneath the support complaint, this is a per-group conditional rate. opened is a 0/1 flag, so a platform's open rate is the sum of that flag over how many codes the platform was sent. Anyone sees that much. What separates candidates is two quiet decisions: keeping the division in decimals so it does not truncate, and scoping the denominator to every code sent, including the ones whose flag never got recorded. Miss the first and every platform comes back at 0; miss the second and every platform with unrecorded flags looks better than it is.
Building it
Step 1: Sum the flag per platform
SUM(opened) over each platform is exactly its number of opens, because the column holds 0 or 1. SUM skips a NULL, so a code whose flag was never recorded adds nothing to the top of the fraction.
Step 2: Count every code sent
COUNT(*) is the denominator: every row for the platform, delivered, failed or bounced, flag recorded or not. Open rate is opens over codes sent, so nothing filters the bottom of the fraction.
Step 3: Scale and sort
Multiply the numerator by 100.0, then divide: the decimal literal forces real division and the factor of 100 turns the fraction into a percentage in one move. Sort by open_rate_pct, highest first.
SELECT platform,
SUM(opened) * 100.0 / COUNT(*) AS open_rate_pct
FROM push_notifs_2fa
GROUP BY platform
ORDER BY open_rate_pct DESC- `SUM(opened) / COUNT(*)`
- Integer division: 1 / 4 truncates to 0 and every platform looks dead. Right denominator, wrong type.
- `SUM(opened) * 100.0 / COUNT(*)`
- The
100.0promotes the expression before dividing, so 1 * 100.0 / 4 is 25.0. Same denominator, right type.
Common follow-up questions
- The team now wants open rate per platform per day. How does the query change, and what starts to matter at 120M rows? (Tests adding a day derived from `sent_at` to the grouping and recognizing the partition filter that suddenly matters.)
- If `platform` values started arriving as 'iOS', 'ios' and 'IOS', how do you keep them in one group? (Tests normalizing the grouping key so casing variants collapse.)
- How would you show only the platforms that sent at least 1,000 codes? (Tests filtering on an aggregate after grouping rather than in `WHERE`.)