# Seen or Ignored > A send is only half the story. Find where the taps actually land. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Our push notification system logs every send along with whether the recipient opened it. Find each platform's fraction of sent notifications that were opened, strongest open rate first, with platforms that land on the same rate falling in alphabetical order. ## Worked solution and explanation ### What this really tests This is a denominator-definition problem wearing a push-notification costume. The metric looks trivial: opens over sends, split by platform. The real question the interviewer is probing: when some sends never report back an open signal (opened is NULL), do those rows belong in your denominator? The ask is the fraction of ALL sent notifications, so they do. The candidates who miss this quietly divide by the wrong number and every platform looks better than it is. > **Trick to solving** > > Reach for AVG(opened) here and you have already lost. AVG divides SUM(opened) by COUNT(opened), and COUNT(opened) silently skips the NULL rows. Your denominator becomes 'sends we tracked', not 'sends we made'. The fix is to spell the denominator out yourself: SUM(opened) over COUNT(*). --- ### Building it #### Step 1: Count the confirmed opens `opened` is a 0/1 flag, so `SUM(opened)` is the count of confirmed opens. A NULL contributes nothing, which is exactly right: an untracked send was not confirmed opened. #### Step 2: Divide by every send Divide by `COUNT(*)`, not `COUNT(opened)`. `COUNT(*)` counts every send for the platform, including the NULL-opened rows, giving the fraction of all sent notifications. The `* 1.0` forces real division so the result is a decimal instead of a truncated integer 0. #### Step 3: Split by platform and rank Break the calculation out per platform and order by the ratio descending so the strongest platforms surface first. --- ### The solution **Per-platform open ratio in one pass** ```sql SELECT platform, SUM(opened) * 1.0 / COUNT(*) AS open_ratio FROM push_notifs GROUP BY platform ORDER BY open_ratio DESC, platform ``` **AVG(opened) (wrong)** Divides by COUNT(opened), which drops every NULL-opened send. Platforms with many untracked sends get an inflated open rate that has no relationship to how many notifications you actually pushed. **SUM(opened) * 1.0 / COUNT(*) (right)** Divides by COUNT(*), so untracked sends stay in the denominator and count as not-opened. This is the true fraction of everything you sent. > **Common pitfall** > > Two traps hide in one line. First, AVG(opened) or COUNT(opened) in the denominator quietly excludes NULL-opened rows and inflates the rate. Second, in engines with integer division, SUM(opened) / COUNT(*) truncates to 0; the * 1.0 (or a CAST) is what keeps it a real ratio. > **Interviewers watch for** > > The tell of a senior candidate is that they pause on the denominator before writing anything. They ask whether an unreported open means not-opened or excluded, and they say out loud why COUNT(*) and not COUNT(opened). Jumping straight to AVG(opened) reads as pattern-matching without thinking about the NULLs. > **Cost analysis** > > At 150,000,000 rows this is a single sequential scan with a hash aggregate on a 3-value column, so memory stays tiny and there is no sort blow-up. Because the grouping key has just a handful of distinct values, the aggregate is effectively free; the scan dominates. For a live dashboard you would back this with a periodic rollup keyed on platform rather than re-scanning the raw table each load. --- ## Common follow-up questions - How would you change this to the open rate among only the notifications that were actually delivered? _(Tests whether the candidate can separate delivery from engagement and adjust the denominator accordingly.)_ - How would you report each platform's open rate week over week to spot a drop after a release? _(Tests window-function fluency and comfort with time-bucketed metrics.)_ - A brand-new platform shows a 100 percent open rate from three sends. How would you keep low-volume platforms from topping the list? _(Tests awareness of small-sample noise in ratio metrics.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/seen_or_ignored) - [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.