Events by Month Across Years

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

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
Meta

Problem

Our traffic in event_data looks seasonal, with the same months running hot year after year. Count every event in each calendar month with all years folded together, from January through December.

Month by month, year by year. The pattern emerges.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

event_data: 10 of 200 rows
event_idevent_typeuser_idevent_timestamppayloadtagsproperties
3059button_click1002026-02-02 01:07:00{"button_id": "cancel", "element_class": "btn-primary", "position": {"x": 13, "y": 7}}["desktop", "web", "returning"]{"os": "Edge", "language": "US", "screen_size": "en", "utm_source": "mobile"}
3118purchase1972026-03-03 02:14:00{"amount": 56.93, "currency": "GBP", "items": [{"sku": "SKU1002", "qty": 3}]}["ios", "premium", "new_user", "organic"]{"country": "Windows", "device_type": "FR", "referrer": "fr", "utm_medium": "1920x1080", "browser": "twitter"}
3177signup2942026-04-04 03:21:00{"signup_method": "apple", "referral_code": "REF3X", "accepted_terms": true}["android", "free", "campaign_a", "referral", "experiment_v1"]{"language": "iOS", "screen_size": "es", "utm_source": "desktop", "session_id": "bing", "os": "organic"}
3236login3912026-05-05 04:28:00{"login_method": "password", "remember_me": true, "mfa_used": false}["web", "returning"]{"device_type": "JP", "referrer": "ja", "utm_medium": "1440x900"}
3295logout4882026-06-06 05:35:00{"action": "logout", "value": 50}["premium", "new_user", "organic"]{"screen_size": "de", "utm_source": "tablet", "session_id": "amazon", "os": "Chrome"}
3354search5852026-07-07 06:42:00{"query": "phone", "results_count": 102, "filters": {"category": "electronics"}}["free", "campaign_a", "referral", "experiment_v1"]{"referrer": "mobile", "utm_medium": "375x812", "browser": "email", "country": "Edge", "device_type": "US"}
3413add_to_cartNULL2026-08-08 07:49:00{"product_id": 1007, "quantity": 2, "price": 58.49, "variant": "medium"}["returning", "campaign_b", "featured", "experiment_v2", "ios"]{"utm_source": "fr", "session_id": "twitter", "os": "Safari", "language": "Windows", "screen_size": "FR"}
3472open7792026-09-09 08:56:00{"action": "open", "value": 80}["new_user", "organic"]{"utm_medium": "bing", "browser": "organic", "country": "iOS"}
3531crash8762026-01-10 09:03:00{"action": "crash", "value": 90}["campaign_a", "referral", "experiment_v1"]{"session_id": "direct", "os": "Firefox", "language": "macOS", "screen_size": "JP"}
3590error9732026-02-11 10:10:00{"action": "error", "value": 100}["campaign_b", "featured", "experiment_v2", "ios"]{"browser": "Chrome", "country": "Android", "device_type": "DE", "referrer": "de", "utm_medium": "tablet"}

Expected output

First 5 of 12 rows
monthevent_count
124
220
318
418
518

Worked solution and explanation

What this really is

This is a cyclic bucket dressed up as a seasonality report. The skill is pulling month of year out of event_timestamp so that every year's January lands in the same bucket. Anyone can count rows. What separates candidates is noticing that event_data spans 2020 through 2026, so a month key that still carries the year gives one row per year per month instead of twelve. Miss it and January's total is split across five or six rows, and the seasonal shape never appears.

Step 1: Strip the year from the timestamp

strftime('%m', event_timestamp) keeps only the month digits, so '2023-01-14' and '2026-01-10' both yield '01'. That one move is what folds the years together.

Step 2: Make the month a number

The month comes back as zero-padded text. Wrap it in CAST(... AS INTEGER) once, in the select list, and name it month, so the report reads 1 through 12 rather than '01' through '12'.

Step 3: Bucket on the name, count every row

GROUP BY month reuses the expression you already named instead of repeating it. COUNT(*) counts every event, including the 25 rows with no user_id; COUNT(user_id) would quietly drop them.

Step 4: Read it January to December

ORDER BY month on the integer gives the calendar walk the reader asked for.

Events per calendar month, all years combined
SELECT CAST(strftime('%m', event_timestamp) AS INTEGER) AS month,
       COUNT(*) AS event_count
FROM event_data
GROUP BY month
ORDER BY month
The version that keeps the year
SELECT strftime('%Y-%m', event_timestamp) AS year_month,
       COUNT(*) AS event_count
FROM event_data
GROUP BY year_month
ORDER BY year_month

Keying on strftime('%Y-%m', ...) returns one row per year and month, so January appears once per year.

Common follow-up questions