Event Count on Key Days
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
We are sizing weekend capacity against the start of the work week, so we need the total number of events in event_data that fell on a Saturday, a Sunday or a Monday. Return each of those three days as its weekday number, an integer, alongside its event count.
Key days. Key event volumes.
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
| day_of_week | event_count |
|---|---|
| 0 | 29 |
| 1 | 29 |
| 6 | 31 |
Worked solution and explanation
Why this problem exists in real interviews
Capacity-planning queries probe two reflexes at once: do you know the day-of-week convention your engine uses (SQLite is Sunday=0), and do you spot that wrapping event_timestamp in strftime defeats partition pruning on a 150M row table? Both decisions surface in the first sentence you should say out loud.
Break down the requirements
Step 1: State the convention
Say out loud: strftime('%w', ts) returns a string from '0' to '6' with Sunday=0, Monday=1 and Saturday=6. Cast it to an integer so IN (0, 1, 6) compares like with like and the output column is an integer.
Step 2: Filter to the two days
WHERE CAST(strftime('%w', event_timestamp) AS INTEGER) IN (0, 1, 6). Use IN, not three OR branches: the intent is clearer and the plan is identical.
Step 3: Group by the same expression
Project the cast expression as day_of_week, then GROUP BY day_of_week. SQLite resolves the alias in GROUP BY; other engines may require the full expression repeated.
Step 4: Aggregate
COUNT(*) per bucket. Three output rows: Sunday, Monday and Saturday. No order was asked for, so don't invent one.
The solution
SELECT
CAST(strftime('%w', event_timestamp) AS INTEGER) AS day_of_week,
COUNT(*) AS event_count
FROM event_data
WHERE CAST(strftime('%w', event_timestamp) AS INTEGER) IN (0, 1, 6)
GROUP BY day_of_weekCOMMON FOLLOW-UP QUESTIONS
Common follow-up questions
- How would you rewrite this to preserve partition pruning on `event_timestamp`? (Tests whether you reach for a generated `day_of_week` column or a range-rewrite (one `BETWEEN` per target week).)
- Bucket events by hour-of-day for Saturday only. (Tests reusing the same `strftime` pattern with `%H` and combining a where-filter with a different group key.)
- Compute the Saturday-to-Monday ratio in a single query. (Tests conditional aggregation: `SUM(CASE WHEN day_of_week = 6 THEN 1 END) * 1.0 / SUM(CASE WHEN day_of_week = 1 THEN 1 END)`.)
- What changes on Postgres versus MySQL versus Snowflake? (Tests cross-engine date-function fluency and indexing-on-expression versus generated columns.)