# Top 2 Active Push Days > Two days stood out from the rest. Which ones? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Walmart ## Problem Our monthly send totals make some months of the year look like they ran far more varied messaging than others, when mostly they just sent more. From `push_notifs`, find how many different push titles went out in each month of the year, pooled across all years, labeled the way our dashboard tiles show it ('Month 3'), most varied 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 12 rows: | send_month | unique_titles | | --- | --- | | Month 9 | 10 | | Month 8 | 10 | | Month 7 | 10 | | Month 12 | 4 | | Month 11 | 4 | ## Worked solution and explanation ### What this is really asking Without the marketing framing, this is a unique-value count over a month-of-year bucket, followed by one cleanup on the label. Bucketing by month is easy. The step that separates candidates is seeing that the same `title` goes out over and over inside one month, so a row count and a unique-title count are two very different numbers. Count rows and you report send volume as if it were variety, and the busiest month wins instead of the most varied one. The quieter trap is the label: `strftime('%m', sent_at)` returns '03', the tiles read 'Month 3', and a label built from the raw value matches none of them. --- ### Break down the requirements #### Step 1: Bucket by month of the year `sent_at` is a timestamp, so grouping on the raw value gives every row its own second. `strftime('%m', sent_at)` folds every date into twelve month buckets and pools the same month across years. It returns a zero-padded string, '01' through '12', and that padding causes trouble in step 3. #### Step 2: Count unique titles, not rows `COUNT(DISTINCT title)` counts each message once per month, however many times it was sent. A plain `COUNT(*)` or `COUNT(title)` rewards the months that simply sent the most pushes, and that answers a different question: volume, not variety. #### Step 3: Strip the padding from the leading side only The tiles read 'Month 3', not 'Month 03'. `TRIM(LEADING '0' FROM ...)` removes only leading zeros, so '03' becomes '3' while '10', '11' and '12' keep their digits. `'Month ' ||` then builds the tile label. If you strip zeros from both ends, '10' becomes '1' and October quietly merges into January. #### Step 4: Sort the twelve by reach Sort on `unique_titles` descending so the most varied month comes first. Two months can end up with the same count. That is fine: the question only asks for most to least varied, so equal months may appear in either order and no tiebreaker is needed. --- ### The solution **TWELVE MONTHS BY UNIQUE TITLES** ```sql SELECT 'Month ' || TRIM(LEADING '0' FROM strftime('%m', sent_at)) AS send_month, COUNT(DISTINCT title) AS unique_titles FROM push_notifs GROUP BY send_month ORDER BY unique_titles DESC ``` > **The distinct swap is the whole tell** > > The move to watch for is writing `COUNT(title)` where `COUNT(DISTINCT title)` belongs. On this data every month sends some titles several times, so the plain count measures volume and reorders the months. An interviewer is watching whether you notice that the two numbers differ at all. > **Trimming both ends eats October** > > A bare `TRIM(BOTH '0' FROM x)` also strips trailing zeros, so '10' becomes '1' and October's titles fold into the 'Month 1' row. Only the leading side has padding, so only the leading side gets trimmed. > **Twelve hash sets, one per month** > > `COUNT(DISTINCT title)` is the expensive step, but there are only twelve month buckets and a few dozen titles, so the engine keeps twelve tiny hash sets and the query stays cheap across the full table. The label is computed once per row in a single pass, and the `GROUP BY` reuses it by alias. **COUNT(`title`)** A title sent ten times in March counts ten times. Months that simply sent more pushes float up, so the order reflects volume, not variety. **COUNT(DISTINCT `title`)** Each title counts once per month, so the order reflects how many different messages each month actually ran. That is the question being asked. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you show variety relative to volume, say unique titles per hundred sends? _(Divide the unique count by `COUNT(*)` in the same bucket, and watch integer division.)_ - What if we wanted the most varied month per `platform`? _(Add `platform` to the grouping, and notice it is stored in mixed case ('Android', 'android'), so it needs normalizing first.)_ - How would you restrict this to a specific date range? _(A half-open predicate on `sent_at` keeps partition pruning intact, for example `sent_at >= '2026-01-01'`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/top_2_active_push_days) - [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.