# The Ones Still Listening > A buzz only counts when someone who is still around answers it. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The engagement team is measuring notification effectiveness for the quarterly product review. Compute the percentage of all push notifications that were both sent to a user with an active account and opened by the recipient. Notifications should still be counted in the denominator even if the user's account record no longer exists. ## Worked solution and explanation ### What this problem is really about Strip the costume and this is a LEFT JOIN survival test. Every push notification has to stay in the denominator, including the ones whose user row was deleted, or the open rate quietly inflates. Anyone can write the conditional sum for the numerator; the real separator is whether you keep the orphaned notifications in the total. Reach for an inner join and you silently drop every notification whose user is gone, shrinking the denominator and reporting a rosier number than reality. > **Trick to solving** > > Drive the query FROM push_notifs and LEFT JOIN to users so no notification ever falls out. Count the whole table with COUNT(*) for the denominator, flag active-and-opened rows with a conditional SUM for the numerator, and turn one side into a real number before dividing so you get a true percentage instead of an integer that truncates to 0. --- ### Walking the requirements #### Step 1: Keep every notification with a LEFT JOIN LEFT JOIN push_notifs to users on user_id so every notification is retained in the denominator even when no matching user record exists. The direction matters: notifications are the anchor table, users is the optional side. #### Step 2: Flag qualifying rows with CASE Flag rows where the joined user's account_status = 'active' AND the notification's opened = 1, using a CASE expression that yields 1 for a match and 0 otherwise, then SUM it. A null opened value falls to the ELSE branch and correctly counts as not opened. #### Step 3: Compute the ratio without integer truncation Divide that conditional sum by COUNT(*) over all notifications. CAST the numerator to REAL (or multiply by 100.0) first so the division happens in floating point, then the multiply by 100 gives the percentage. #### Step 4: Return one scalar percentage The result is a single scalar row: one column, one value, no grouping and no ordering. There is nothing to sort because the whole table collapses to one number. That single-row shape is exactly what a review-deck metric wants. --- ### The solution **Active user open rate** ```sql SELECT CAST(SUM(CASE WHEN u.account_status = 'active' AND pn.opened = 1 THEN 1 ELSE 0 END) AS REAL) * 100.0 / COUNT(*) AS active_opened_pct FROM push_notifs pn LEFT JOIN users u ON pn.user_id = u.user_id ``` > **Cost analysis** > > The anchor table has 50M rows (about 5 GB). The whole query is a single scan of push_notifs with a hash lookup into the smaller users dimension, then one aggregation pass, so it stays cheap even at scale. An index on users.user_id keeps the probe side fast; there is no sort and no grouping to spill. > **Interviewers watch for** > > The tell of a senior candidate here is the join direction. Weaker answers write an inner join and never notice the orphaned notifications vanishing from the denominator. The second tell is the cast: dividing two integer counts truncates to 0, and interviewers watch for whether you convert to REAL (or use 100.0) before the divide. > **Common pitfall** > > The most common wrong answer uses an inner join, so notifications whose user_id no longer resolves get dropped from the total. The percentage then rises above the truth because the denominator shrank while the numerator did not. The second most common bug is integer division: SUM(...) / COUNT(*) with both sides integers returns 0. **INNER JOIN (wrong)** Notifications whose user was deleted disappear from COUNT(*). The denominator shrinks, so the reported open rate is higher than reality and the metric flatters engagement. **LEFT JOIN (correct)** Every notification stays in COUNT(*) regardless of whether its user row survives. The denominator reflects all sends, so the percentage is honest. --- ## Common follow-up questions - Some notifications point at users who were hard-deleted. Should those still count, and how does your join keep them in the denominator? _(Tests whether the candidate treats a missing dimension row as a real category rather than an error, and understands the LEFT JOIN keeps them.)_ - The opened column has nulls as well as 0 and 1. Walk me through why your CASE treats a null as not opened rather than dropping the row. _(Tests understanding of null handling in aggregation and the difference between 0 and null in opened.)_ - At 50M notifications and 4M users, what index would you add so the join probe does not force a full scan of the dimension table? _(Tests indexing and selectivity reasoning at 50M rows.)_ - The team now wants this open rate split per platform. What changes, and how do you keep orphaned notifications counted within each platform's total? _(Tests whether the candidate can extend a scalar metric into a grouped breakdown without breaking the denominator logic.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_still_listening) - [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.