# When They Opened > Push after push goes out. Some months, people actually read them. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Meta ## Problem Each row of `push_notifs` is one push notification, recording when it was sent and whether it was opened. Find how many notifications were opened in each month of the year, busiest month first. ## 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 9 rows: | month | opened_count | | --- | --- | | 7 | 9 | | 4 | 9 | | 12 | 7 | | 6 | 6 | | 8 | 1 | ## Worked solution and explanation ### What this really is Take away the engagement story and this is a tally of a three-valued flag into month buckets you manufacture from a text timestamp. Anyone writes `COUNT(*)`. What separates candidates is the grain and the filter. The bucket is the month of the year, pooled across every year the table spans. Only rows with `opened = 1` are opens. If you group on the raw `sent_at`, you get one row per send. If you group by year and month, January splits five ways. If you count every row, each month absorbs its unopened and unflagged sends. Then comes the order: busiest first means sorting by `opened_count`, not by the calendar. ### The trap > **`strftime` returns text, not a number** > > In SQLite, `strftime('%m', sent_at)` returns zero-padded text, from '01' to '12'. If you leave it uncast, your month column reads '01' where the answer has 1, and any comparison against a number such as `= 2` silently matches nothing. Cast once, in the expression that defines the bucket, and every later reference sees an integer. > **Count the opens, not the notifications** > > `opened` holds 1, 0 or NULL. `WHERE opened = 1` keeps only confirmed opens, and the NULL rows fall out with the zeros because NULL = 1 is unknown, not true. `SUM(opened)` also skips NULLs, but it keeps months with no opens as rows: months 2, 10 and 11 come back with a 0 that the answer does not have. > **Month of the year, not year and month** > > `sent_at` runs from 2022 into 2026, so `strftime('%Y-%m', sent_at)` quietly answers a different question: one row per calendar month per year. The preview has nine rows keyed from 1 to 12, which tells you every January lands in the same bucket. A strong candidate reads that off the preview instead of asking. ### Building it #### Step 1: Manufacture the month bucket Derive the month with `CAST(strftime('%m', sent_at) AS INTEGER)`. Casting here, once, means the `SELECT`, the `GROUP BY` and the `ORDER BY` all see a clean integer from 1 to 12. #### Step 2: Keep only the opens `WHERE opened = 1` drops the zeros and the NULL flags before anything is counted. That way `COUNT(*)` counts confirmed opens, and a month with no opens never becomes a row. #### Step 3: Tally and sort by volume `GROUP BY month` collapses each calendar month into one row, `COUNT(*)` tallies its opens, and `ORDER BY opened_count DESC` puts the busiest month on top. Months 1, 4 and 7 tie at 9 opens. The question only asks for busiest first, so their order among themselves is free. **Per-month opened counts** ```sql SELECT CAST(strftime('%m', sent_at) AS INTEGER) AS month, COUNT(*) AS opened_count FROM push_notifs WHERE opened = 1 GROUP BY month ORDER BY opened_count DESC ``` **Filter, then count** `WHERE opened = 1` followed by `COUNT(*)` states the intent where a reviewer can see it. NULLs and zeros are excluded by the predicate, and the count runs only over rows you have already vouched for. **Sum the flag** `SUM(opened)` over the whole table assumes the flag only ever holds 0 or 1, and it hides the filter inside the aggregate. It also keeps months 2, 10 and 11 as zero rows, which is three rows the answer does not contain. > **Busiest first is a sort on the count** > > Calendar order is the reflex, because the bucket looks like the key. Here the key is `opened_count DESC`. Months 1, 4 and 7 tie at 9 opens, and months 5 and 8 tie at 1. The question says nothing about ties, so any order among those months is correct. You can add `month` as a second key if you want a stable dashboard, but do not claim the question requires it. > **Month of year touches every partition** > > At 100M rows the plan is a full scan. The table is partitioned on `sent_at`, so a query scoped to a date range would prune partitions, but a month-of-year rollup touches every one of them. That is the argument for a nightly pre-aggregated table keyed by month that dashboards read instead. An index on (`opened`, `sent_at`) also lets the engine skip the unopened rows without touching the heap. ## Common follow-up questions - The current query pools every year's January together. How would you report opened counts per month within each year instead? _(Tests whether the candidate adds the year as a second grouping key, and whether they notice the grain change.)_ - If a chunk of rows has `opened` set to NULL because delivery never confirmed, how does that change your count, and why does `SUM(opened)` return more rows than your query? _(Tests NULL semantics in comparisons and aggregates, and whether the candidate notices the zero-open months.)_ - This runs on a dashboard every few minutes over 100M rows. What would you pre-compute so it stops scanning the raw table? _(Tests indexing and pre-aggregation strategy on a very large partitioned table.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/when_they_opened) - [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.