# Did Anyone Actually Read It? > A push isn't a win until a thumb taps it. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## 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. ## 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 **Normalize, filter, group, count** ```sql 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 ``` > **Cost at 80M rows** > > With `push_notifs` at 80,000,000 rows, wrapping status in LOWER() and `sent_at` in strftime() makes both predicates non-sargable, so a plain index on the raw columns will not be used and the engine falls back to a scan. At this scale, prefer a range predicate on `sent_at` (`sent_at` >= '2026-01-01' AND `sent_at` < the next year) so the partition pruning on `sent_at` kicks in, and back the status and platform normalization with functional indexes on LOWER(status) and LOWER(platform) so the grouped filter can push down instead of scanning the whole table. > **The dirty-text trap** > > The single most common miss is comparing status = 'delivered' directly, which quietly discards every 'Delivered' and 'DELIVERED' row. The subtler cousin is grouping by the raw platform: 'android', 'Android', and 'ANDROID' become three separate rows instead of one, and the count for the true platform is scattered across them. Normalize with LOWER() in BOTH the status filter and the GROUP BY. > **The seniority tell** > > Interviewers watch for whether you notice the case inconsistency without being told twice. Flagging that status is messy is table stakes; the tell for seniority is proactively asking whether platform is dirty too and normalizing it in the grouping key. They also check that opened = 1, the delivered filter, and the year window are all ANDed, not accidentally ORed. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/did_anyone_actually_read_it) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.