Did Anyone Actually Read It?

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

Meta

Problem

List each platform's number of push notifications in push_notifs that were delivered and then opened, most first. We only count what went out in 2026, and our status and platform values were logged with careless capitalization, so 'Delivered' and 'delivered' are one outcome and 'Android' and 'android' are one platform.

A push isn't a win until a thumb taps it.

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

What this is really testing

This is a filtered count per bucket in an engagement-dashboard costume, and the costume hides dirty text. Anyone can write COUNT(*) behind three ANDed conditions. What separates candidates is noticing that status and platform were never normalized on write: this year's wins include rows logged as 'Delivered', and the android wins are logged as 'Android'. Compare status = 'delivered' verbatim and two real wins vanish, ios drops from 5 to 4 and basic from 4 to 3, and nothing in the output tells you anything is missing.

Break it down

Step 1: Scope to 2026

push_notifs reaches back several years, and those years hold plenty of opened, delivered notifications. strftime('%Y', sent_at) = '2026' pulls the year out of the text timestamp and keeps only this year's sends. Drop it and history swells every platform's count.

Step 2: Normalize status, require the open

LOWER(status) = 'delivered' folds 'Delivered' and 'delivered' into one match. opened = 1 keeps only recorded opens; a NULL in opened fails the equality on its own, so blank rows fall away with the zeros.

Step 3: Bucket on the normalized platform

GROUP BY LOWER(platform) gives each platform one bucket whatever its casing, and selecting the same expression reports it as 'android', not 'Android'. COUNT(*) AS opened_count tallies the survivors and ORDER BY opened_count DESC puts the biggest platform first. android and web tie at 3, and either order is right.

The solution

SELECT LOWER(platform) AS platform,
       COUNT(*) AS opened_count
FROM push_notifs
WHERE opened = 1
  AND LOWER(status) = 'delivered'
  AND strftime('%Y', sent_at) = '2026'
GROUP BY LOWER(platform)
ORDER BY opened_count DESC

Verbatim match

WHERE status = 'delivered' with GROUP BY platform: ios 4, basic 3, 'Android' 3, web 3. The two wins logged as 'Delivered' are gone.

Normalized

LOWER(status) and LOWER(platform): ios 5, basic 4, android 3, web 3, one lowercase name per platform.

Common follow-up questions

• If some `status` values arrive as 'delivered ' with trailing whitespace, does `LOWER` still match them? (Tests that case folding and `TRIM` are separate cleanups.)
• How would you report each platform's open rate: wins over all delivered notifications? (Tests moving the open condition into a conditional count so the denominator survives the filter.)
• Given `LOWER(status)` and `strftime` on `sent_at` defeat ordinary indexes, how do you keep this fast at 80M rows? (Tests sargable range predicates and partition pruning.)