# The Open Question > Push sent. How many opened? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Meta ## Problem Dates on which not a single one of our push notifications was opened should not appear at all. For every other send date in `push_notifs`, report the open rate: the share of that day's notifications that were opened, where every row counts as a send even if it failed or its open flag is blank. ## Tables ### push_notifs - 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 | | --- | --- | --- | --- | --- | --- | --- | --- | | 1053 | 100 | New message from team | android | pending | 2026-02-02 01:07:00 | 0 | winback_feb | | 1106 | 197 | Weekly digest ready | web | failed | 2026-03-03 02:14:00 | 0 | promo_summer | | 1159 | 294 | Price drop alert | iOS | bounced | 2026-04-04 03:21:00 | 1 | onboard_v2 | | 1212 | 391 | Security alert | Android | Delivered | 2026-05-05 04:28:00 | 0 | NULL | | 1265 | 488 | Payment received | WEB | FAILED | 2026-06-06 05:35:00 | NULL | loyalty_2024 | | 1318 | 585 | Complete your profile | basic | delivered | 2026-07-07 06:42:00 | 1 | NULL | | 1371 | 682 | New feature available | premium | pending | 2026-08-08 07:49:00 | 0 | reengagement | | 1424 | 779 | Subscription renewal | ios | failed | 2026-09-09 08:56:00 | 0 | NULL | | 1477 | 876 | Flash sale: 50% off | android | bounced | 2026-01-10 09:03:00 | 1 | flash_sale | | 1530 | 973 | Your order shipped! | web | Delivered | 2026-02-11 10:10:00 | NULL | retention_q1 | ## Expected output First 5 of 56 rows: | send_date | open_rate | | --- | --- | | 2022-03-24 | 1 | | 2022-06-07 | 1 | | 2022-06-11 | 1 | | 2026-01-17 | 0.5 | | 2026-01-28 | 0.5 | ## Worked solution and explanation ### What this really is This is a post-aggregation filter wearing a metrics-dashboard costume. Anyone can average `opened` per day; the thing that separates candidates is the exclusion rule: keep only the days that saw at least one open. That test runs on an aggregate (`SUM(opened)`), not on a single row, so it has to live in HAVING, after the grouping. Reach for WHERE instead and you strip rows before they are grouped: a day with one open and nine misses would keep only the opened row and report a 100% rate. Same keyword confusion, silently corrupted daily numbers. --- ### Break down the requirements #### Step 1: Normalize `sent_at` to a send date `DATE(sent_at)` collapses each timestamp to its calendar send date, so everything sent on the same day lands in one bucket. `sent_at` is stored as text here, and `DATE()` reads the date portion cleanly. #### Step 2: Group by date and compute the rate `GROUP BY DATE(sent_at)` with `SUM(opened) * 1.0 / COUNT(*)` is opens over total sent for the day. The `* 1.0` forces real division; without it, integer math floors 1/2 to 0 and every rate collapses to 0. #### Step 3: Keep only days with at least one open `HAVING SUM(opened) > 0` drops days where nothing was opened. Because it tests a per-day aggregate, it can only run after the grouping, which is exactly the job HAVING exists for. A day whose opens are all null also fails this test, since SUM over only nulls is null and null is not greater than 0. --- ### The solution **Daily open rate with a post-aggregation filter** ```sql SELECT DATE(sent_at) AS send_date, SUM(opened) * 1.0 / COUNT(*) AS open_rate FROM push_notifs GROUP BY DATE(sent_at) HAVING SUM(opened) > 0 ``` > **A bounded window prunes partitions** > > At 100,000,000 rows a full scan is the cost driver. The table is partitioned on `sent_at`, so a bounded reporting window prunes to the relevant day partitions instead of touching the whole history. A covering index on the grouping and aggregated columns removes random I/O, and for a dashboard that reruns this constantly, a materialized daily rollup pays for itself fast. > **Say why the filter cannot go in WHERE** > > The tell is where the at-least-one-open filter goes. Strong candidates reach for HAVING without pausing and can say in one sentence why WHERE would be wrong here. They also notice `opened` carries nulls and confirm the denominator still counts those rows before declaring the query done. > **WHERE inflates every surviving day** > > The classic miss is filtering with WHERE instead of HAVING. WHERE runs before grouping, so it removes rows the aggregate needed and quietly inflates every surviving day's rate. The second miss is forgetting the `* 1.0`, which turns real open rates into a column of zeros via integer division. --- ## Common follow-up questions - If `opened` is null for some notifications, how does that change the numerator versus the denominator of your open rate? _(Probes understanding of how NULL propagates through SUM and COUNT on the `opened` column.)_ - `push_notifs.user_id` has roughly 6,000,000 distinct values. What index strategy would you use to avoid a full scan when the report is scoped to one user? _(Tests indexing knowledge specific to the high-cardinality `user_id` column in `push_notifs`.)_ - If the at-least-one-open rule became keep only days above the median daily open rate, how would you restructure the query? _(Tests the ability to replace a static HAVING threshold with a dynamic, subquery-based one.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_open_question) - [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.