# Proof of Presence > Two-factor sent. How many confirmed? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Meta ## Problem List each client platform's two-factor confirmation rate and ignored rate from `push_notifs_2fa`, both as two-decimal shares of the pushes that were actually delivered, with the highest confirmation rate first. The security analyst who asked counts a push as confirmed when the user opened it and as ignored only when the client recorded it as unopened, so a delivery with no recorded outcome stays in the total but is neither. ## Tables ### push_notifs_2fa - notif_id INTEGER - user_id INTEGER - title TEXT - platform TEXT - status TEXT - sent_at TEXT - opened INTEGER - campaign TEXT Sample rows (10 of 200): | notif_id | user_id | title | platform | status | sent_at | opened | campaign | | --- | --- | --- | --- | --- | --- | --- | --- | | 1001 | 101 | Your 2FA code | android | delivered | 2026-02-02 01:07:00 | 1 | login_feb | | 1002 | 102 | Your 2FA code | android | delivered | 2026-02-03 02:10:00 | 1 | login_feb | | 1003 | 103 | Your 2FA code | android | delivered | 2026-02-04 03:15:00 | 0 | login_feb | | 1004 | 104 | Your 2FA code | ios | delivered | 2026-02-05 04:20:00 | 1 | login_feb | | 1005 | 105 | Your 2FA code | ios | delivered | 2026-02-06 05:25:00 | 0 | login_feb | | 1006 | 106 | Your 2FA code | web | delivered | 2026-02-07 06:30:00 | 1 | login_mar | | 1007 | 107 | Your 2FA code | web | delivered | 2026-02-08 07:35:00 | 0 | login_mar | | 1008 | 108 | Your 2FA code | web | delivered | 2026-02-09 08:40:00 | NULL | login_mar | | 1009 | 109 | Your 2FA code | ios | failed | 2026-02-10 09:45:00 | 0 | NULL | | 1010 | 110 | Your 2FA code | android | bounced | 2026-02-11 10:50:00 | 1 | login_mar | ## Expected output First 3 of 3 rows: | platform | confirmation_rate | ignored_rate | | --- | --- | --- | | android | 0.67 | 0.33 | | ios | 0.5 | 0.5 | | web | 0.33 | 0.33 | ## Worked solution and explanation ### What this problem really is This is a three-state flag dressed up as a security dashboard. `opened` holds a 1 for a confirmed push, a 0 for one the client saw go unopened, and a blank when the client never reported back. You have to split that one column into two shares over the same delivered denominator. Anyone can filter to delivered and split by `platform`. What separates people is counting the right thing: `COUNT(opened)` counts the zeros too and scores Android at 1.00, and an ignored count that sweeps in the blanks doubles web's ignored rate. Both look like plausible dashboard numbers, which is exactly why nobody catches them. **Counts the column** `COUNT(opened)` skips only NULLs, so Android's three delivered pushes (1, 1, 0) give 3 and a confirmation rate of 1.00. **Counts the confirmations** `COUNT(NULLIF(opened, 0))` turns each 0 into a NULL first, so only the 1s survive: 2 of 3, a rate of 0.67. ### Building it #### Step 1: Filter to delivered pushes first `WHERE status = 'delivered'` fixes the denominator. It also removes a bounced Android push that is flagged as opened: the seed contradicts itself there, and skipping the filter quietly lifts Android to 0.75. #### Step 2: Split at `platform` One row per client platform. `COUNT(*)` is then that platform's delivered pushes, blanks included, which is what both shares divide by. #### Step 3: Count confirmations with `NULLIF` `COUNT` ignores NULLs, so `NULLIF(opened, 0)` hides the unopened pushes from it. A blank flag is already NULL and stays out. What is left is exactly the 1s. #### Step 4: Count ignored pushes with a `CASE` `SUM(CASE WHEN opened = 0 THEN 1 ELSE 0 END)` scores only an explicit 0. A blank fails `opened = 0` and adds nothing, so web's unknown push lands in neither share and web reads 0.33 ignored, not 0.67. #### Step 5: Divide, round, sort `1.0 *` keeps each division fractional, `ROUND(..., 2)` gives the two decimals the analyst reads, and the result is sorted on `confirmation_rate` descending. **Two shares over one delivered denominator** ```sql SELECT platform, ROUND(1.0 * COUNT(NULLIF(opened, 0)) / COUNT(*), 2) AS confirmation_rate, ROUND(1.0 * SUM(CASE WHEN opened = 0 THEN 1 ELSE 0 END) / COUNT(*), 2) AS ignored_rate FROM push_notifs_2fa WHERE status = 'delivered' GROUP BY platform ORDER BY confirmation_rate DESC ``` > **A blank flag belongs to the denominator only** > > The two shares do not have to add up to 1. Web's delivered pushes are 1, 0 and blank: 0.33 confirmed, 0.33 ignored, and the missing third is the push nobody reported on. If your two columns always sum to 1, you have assigned the blank somewhere it does not belong. > **Integer division zeroes every share** > > Drop the `1.0 *` and `2 / 3` is `0` on most engines. Every platform reports 0.00, the sort collapses into a three-way tie, and the query still runs without a single error. > **Ask what a blank means before writing it** > > The strong signal is a candidate who asks what a missing `opened` means, then shows the answer in both columns: in `COUNT(*)`, out of both numerators. Candidates who never ask usually fold it into one side without noticing. > **One pass, three output rows** > > At 600M rows across 730 `sent_at` partitions this query has no date filter, so it scans every partition once. It is still a single filtered aggregation that collapses to three rows. Adding a `sent_at` window is what lets the engine prune partitions. ## Common follow-up questions - The analyst now wants the same two shares per platform per month. What changes? _(Adding a time grain from `sent_at` and checking the partition pruning it buys.)_ - Some clients start sending `opened = 2` for a push dismissed by the OS. Does your confirmation count still hold? _(Whether `COUNT(NULLIF(opened, 0))` still means 'confirmed' once the flag gains a fourth state.)_ - How would you surface the share of deliveries with no recorded outcome as a third column? _(Making the blank bucket explicit without double-counting it.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/proof_of_presence) - [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.