# The Notification Lifecycle > Sent, opened, ignored. What happened after the alert went out? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Our push notification system records a delivery status for every message, but that status is entered inconsistently, so the same outcome shows up in different letter cases. For each registered user, count how many of their notifications were delivered, how many were opened, and how many failed, where an open is captured by its own flag rather than the status text. ## Worked solution and explanation ### What this is really about Under the notification-dashboard costume, this is a conditional pivot: collapse many notification rows per user into one row with three counts. Everyone can write the CASE sums. The two things that actually separate candidates are hiding in the data: the status text is logged in mixed casing, and 'opened' is not a status at all. Match status case-sensitively and you silently undercount every 'Delivered' and 'FAILED'; treat opened as a status value and the column comes back all zeros because no such status exists. --- ### The two traps > **Case-sensitive matching undercounts** > > Look at the sample: 'delivered', 'Delivered', 'failed', 'FAILED' all appear. A plain status = 'delivered' matches only the exact-lowercase rows and drops the rest, so a user with three delivered notifications might report one. Lower-case both sides before comparing. This is the kind of dirty-data detail interviewers plant on purpose. > **Opened is a flag, not a status** > > There is an opened column (0/1, sometimes null), and there is no 'opened' status. Writing SUM(CASE WHEN status = 'opened' ...) returns 0 for everyone because that string never occurs. Opens are a separate signal: count opened = 1, and let null fall through the ELSE to 0. **Naive (both traps)** SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) and SUM(CASE WHEN status = 'opened' THEN 1 ELSE 0 END). Delivered undercounts on mixed casing; opened is always 0. **Correct** SUM(CASE WHEN LOWER(status) = 'delivered' THEN 1 ELSE 0 END) and SUM(CASE WHEN opened = 1 THEN 1 ELSE 0 END). Casing normalized, opens read from the real signal. ### Building it #### Step 1: Restrict to registered users Join push_notifs to users on user_id. The inner join is the 'registered users only' filter: any notification whose user_id has no matching row is dropped, and users with no notifications never appear. #### Step 2: One row per user GROUP BY user_id collapses each user's many notification rows into a single output row. The three CASE sums are evaluated within each group. #### Step 3: Pivot the three metrics delivered_count and failed_count come from LOWER(status) so casing cannot split a count; opened_count comes from opened = 1 so a null flag counts as not opened. Three independent conditional sums, one pass over the group. ### The solution **Conditional pivot per user** ```sql SELECT pn.user_id, SUM(CASE WHEN LOWER(pn.status) = 'delivered' THEN 1 ELSE 0 END) AS delivered_count, SUM(CASE WHEN pn.opened = 1 THEN 1 ELSE 0 END) AS opened_count, SUM(CASE WHEN LOWER(pn.status) = 'failed' THEN 1 ELSE 0 END) AS failed_count FROM push_notifs pn JOIN users u ON pn.user_id = u.user_id GROUP BY pn.user_id ``` > **Cost at scale** > > At 120,000,000 notifications and 15,000,000 users this is a single grouped hash aggregate over one join. push_notifs is partitioned by sent_at, so a real dashboard would scope to a date range and prune partitions before the join. The three CASE sums add no extra scan; they fold into the same aggregate pass. A covering index on (user_id, status, opened) lets the group-by run without touching the heap. > **Interviewers watch for** > > The tell is whether you notice the dirty data before writing SQL. Strong candidates eyeball the sample, spot the mixed casing and the standalone opened flag, and ask about them. Jumping straight to status = 'delivered' / status = 'opened' produces a query that runs, returns plausible-looking numbers, and is quietly wrong on both columns. --- ## Common follow-up questions - Add an open rate per user: opened over delivered. How do you avoid dividing by zero for users with no deliveries? _(Tests whether they can extend the pivot to a derived open rate and handle division safely.)_ - Now include registered users who received no notifications at all, showing zeros. What changes in the join? _(Probes left join versus inner join semantics and COALESCE for zero-fill.)_ - This dashboard refreshes hourly on an append-only table partitioned by sent_at. How would you maintain the counts incrementally instead of re-scanning history? _(Tests incremental aggregation design against a partitioned, append-only table.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_notification_lifecycle) - [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.