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

Growth is reviewing which platforms actually drove push engagement in 2026, setting aside anything sent in earlier years. A notification counts as a real win only when it reached the device (status = 'delivered', though the data is careless about capitalization) and the user opened it (opened = 1). For each platform, return the count of these wins as opened_count, from the most to the fewest.

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 multi-predicate count sliced by a dimension, wearing an engagement-dashboard costume. Anyone can write COUNT(*) with a WHERE clause. The real skill being probed: can you spot that the text columns are dirty and normalize them before you filter and before you group? The status field arrives as 'delivered', 'Delivered', and 'DELIVERED', and the platform field arrives as 'android', 'Android', and 'ANDROID'. Compare status = 'delivered' directly and you silently drop every capitalized win. Group by the raw platform and one real platform explodes into three phantom groups that will never match the expected three-row answer.

Break it down

Step 1: Scope to the target year

WHERE strftime('%Y', sent_at) = '2026' scopes to notifications sent during the target year by pulling the year out of the timestamp. The 2025 row in the sample is a deliberate trap: it is delivered and opened, but it falls outside the window and must not be counted.

Step 2: Normalize status, require the open

AND LOWER(status) = 'delivered' folds every capitalization of the status into one match, and AND opened = 1 keeps only the notifications a user actually tapped. Because opened = 1 is a strict equality, NULL and 0 both fall away for free, so failed, bounced, and unopened rows never survive.

Step 3: Group on the normalized platform and count

GROUP BY LOWER(platform) collapses the case variants of each platform into a single bucket before counting, and COUNT(*) AS opened_count tallies the survivors per bucket. ORDER BY opened_count DESC, platform ASC puts the busiest platform on top and settles any ties deterministically by name.

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, platform ASC

Common follow-up questions

• If some status values arrive as 'delivered ' with trailing whitespace, would LOWER() still match them, and how would you harden the filter? (Tests awareness that LOWER() alone does not fix leading or trailing whitespace, and that TRIM may be needed.)
• How would you also report the open rate per platform, dividing opened wins by total delivered notifications? (Tests whether the candidate can extend a grouped count with a second dimension or a rate.)
• Given LOWER(status) and strftime on `sent_at` defeat ordinary indexes, what index and partitioning strategy keeps this query fast at 80M rows? (Tests indexing knowledge specific to non-sargable predicates on status and `sent_at` at scale.)