# Seen and Unseen > Every ping lands somewhere. Not every one gets read. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem The engagement team wants to know which platforms actually get push notifications read. For each platform, give the open rate as a percentage of notifications sent, highest first. ## Worked solution and explanation ### What this really is Beneath the dashboard label, this is a per-group conditional rate. `opened` is a 0/1 flag, so a platform's open rate is nothing more than the sum of that flag divided by how many notifications the platform received. Anyone can see that much. What separates candidates is two quiet decisions: keeping the division in floating point so it does not truncate to zero, and scoping the denominator to every notification sent rather than only the ones that were opened. Miss the first and every rate comes back 0; miss the second and each platform reports 100 percent. > **Force floating point early** > > In most engines SUM(opened) and COUNT(*) are both integers, so SUM(opened) / COUNT(*) does integer division and 1 / 4 collapses to 0. Multiplying by 100.0 before dividing promotes the whole expression to a float, so 1 * 100.0 / 4 is 25.0. Keep the 100.0 on the numerator, ahead of the division, not tacked on after it. --- ### Building it #### Step 1: Sum the flag per platform `SUM(opened)` gives the opened count for each platform. Because the column is 0/1, the sum is exactly the number of opens. A NULL here is skipped by SUM, which quietly treats an unknown as not opened once the group has any real value. #### Step 2: Count everything sent `COUNT(*)` is the denominator: every row for the platform, opened or not. This is the deliberate scoping choice. Open rate is opens over total sent, so COUNT(opened) would be wrong because it drops the unopened rows from the bottom of the fraction. #### Step 3: Turn the ratio into a percentage Multiply the numerator by 100.0 first, then divide. The float literal forces real division and the times 100 scales the fraction into a percentage in a single move. Order the result by open rate, highest first, and break equal rates by platform name so the output is deterministic. **Per-platform open rate** ```sql SELECT platform, SUM(opened) * 100.0 / COUNT(*) AS open_rate_pct FROM push_notifs GROUP BY platform ORDER BY open_rate_pct DESC, platform ``` **SUM(opened) / COUNT(*)** Integer division: 1 / 4 truncates to 0 and every platform looks dead. Correct denominator, wrong type. **SUM(opened) * 100.0 / COUNT(*)** The 100.0 promotes the expression to float before dividing, so 1 * 100.0 / 4 is 25.0. Same denominator, right type. > **COUNT(opened) is not COUNT(*)** > > The seductive wrong answer is SUM(opened) / COUNT(opened). That denominator only counts rows where opened is non-null, which for an open-rate metric silently inflates every platform. Total sent is COUNT(*); do not let a NULL-skipping COUNT sneak into the denominator. --- > **At 120M rows** > > The table is partitioned on sent_at and holds 120,000,000 rows, but this metric touches every partition because there is no date filter. The saving grace is that platform has only a handful of distinct values, so the aggregation collapses 120M rows into a few groups in a single scan. If a dashboard runs this hourly, pre-aggregate opens and sends per platform per day and let the dashboard do the final divide. > **What the interviewer is watching** > > Two tells separate seniors here. First, do you reach for the float multiply without being reminded, because integer division has burned you before. Second, do you choose COUNT(*) over COUNT(opened) and can you justify it in one sentence. A clean per-platform percentage on the first try, ordered highest first, says you have shipped metric queries in production. ## Common follow-up questions - The team now wants open rate per platform per day. How does the query change, and what starts to matter at 120M rows? _(Tests whether they add sent_at to the grouping and recognize the partition filter that suddenly matters.)_ - Some platform values arrive as 'iOS', 'ios', and 'IOS'. How do you keep those from splitting into separate groups? _(Tests data-cleaning instinct: normalizing the grouping key so casing variants collapse.)_ - How would you show only the platforms that sent at least 1,000 notifications? _(Tests filtering on an aggregate after grouping rather than in WHERE.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/seen_and_unseen) - [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.