Top 2 Active Push Days

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Two days stood out from the rest. Which ones?

Tables

push_notifs

Columns: notif_id INTEGER, user_id INTEGER, title TEXT, platform TEXT, status TEXT, sent_at TEXT, opened INTEGER, campaign TEXT

Expected output

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

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

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'`.)