The Perennials
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
- Amazon
Problem
The analytics team is picking out the platform's always-on event types, the ones that keep firing month after month rather than in short bursts. Return the event types that were active in at least 12 separate calendar months.
Some events span seasons.
Tables
event_data
Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT
| event_id | event_type | user_id | event_timestamp | payload | tags | properties |
|---|---|---|---|---|---|---|
| 3059 | button_click | 100 | 2026-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"} |
| 3118 | purchase | 197 | 2026-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"} |
| 3177 | signup | 294 | 2026-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"} |
| 3236 | login | 391 | 2026-05-05 04:28:00 | {"login_method": "password", "remember_me": true, "mfa_used": false} | ["web", "returning"] | {"device_type": "JP", "referrer": "ja", "utm_medium": "1440x900"} |
| 3295 | logout | 488 | 2026-06-06 05:35:00 | {"action": "logout", "value": 50} | ["premium", "new_user", "organic"] | {"screen_size": "de", "utm_source": "tablet", "session_id": "amazon", "os": "Chrome"} |
| 3354 | search | 585 | 2026-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"} |
| 3413 | add_to_cart | NULL | 2026-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"} |
| 3472 | open | 779 | 2026-09-09 08:56:00 | {"action": "open", "value": 80} | ["new_user", "organic"] | {"utm_medium": "bing", "browser": "organic", "country": "iOS"} |
| 3531 | crash | 876 | 2026-01-10 09:03:00 | {"action": "crash", "value": 90} | ["campaign_a", "referral", "experiment_v1"] | {"session_id": "direct", "os": "Firefox", "language": "macOS", "screen_size": "JP"} |
| 3590 | error | 973 | 2026-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
| event_type |
|---|
| add_to_cart |
| button_click |
| checkout_start |
| crash |
| error |
Worked solution and explanation
What this is really asking
Strip the business framing and this is a distinct-month coverage count per event type. The skill under test: can you express "how many separate calendar months did this event touch" as COUNT(DISTINCT year-month) behind a GROUP BY, and gate it with HAVING. Anyone can group by event_type; the separation is refusing to count rows. An event that fires ten thousand times across two frantic months is a burst, not an always-on event, and only counting DISTINCT months tells the two apart.
Building it up
Step 1: Turn each timestamp into a month key
strftime('%Y-%m', event_timestamp) collapses every event in, say, March 2024 to '2024-03'. Using year AND month together is the whole game: 'Feb 2023' and 'Feb 2024' must stay separate, or you undercount an event's real reach.
Step 2: Group by type, count distinct month keys
GROUP BY event_type, then COUNT(DISTINCT ...) over that year-month key gives each type's month coverage. The DISTINCT is what makes a hundred logins in one month count once for that month.
Step 3: Keep the always-on types
HAVING keeps only types that reached at least 12 separate months, roughly a full year of coverage: everything below the bar is a burst, not an always-on event.
The solution
SELECT event_type
FROM event_data
GROUP BY event_type
HAVING COUNT(DISTINCT strftime('%Y-%m', event_timestamp)) >= 12- COUNT(*) >= 12
- Counts every event row. A type that spiked in just two months but logged thousands of rows sails through, mislabeled as always-on.
- COUNT(DISTINCT month) >= 12
- Counts calendar months touched. The two-month spike scores 2 and is correctly dropped; only genuinely recurring types survive.
Common follow-up questions
- How would you also return each type's month count, and restrict it to activity in a specific year? (Tests moving the derived metric into the SELECT and adding a WHERE window without breaking the DISTINCT count.)
- The team now wants types active in at least 12 months that have gone quiet in the last 90 days. How do you express 'quiet recently' alongside the coverage rule? (Tests combining a HAVING coverage threshold with a MAX(`event_timestamp`) recency condition.)
- `event_type` is skewed, with a few types dominating the rows. Does that change your aggregation cost, and how would you keep it cheap? (Tests understanding of skew in hash aggregation and partial pre-aggregation.)