Mobile Event Counts
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
- Apple
Problem
Looking only at the events tagged as 'mobile', count how many events were recorded under each event type, so that every event type appearing among those mobile events comes back with its own total.
Mobile engagement, device by device.
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 | event_count |
|---|---|
| button_click | 2 |
| checkout_start | 4 |
| crash | 6 |
| signup | 8 |
| video_play | 3 |
Worked solution and explanation
Why this problem exists in real interviews
Extracting insights from event_data.event_type grouped by event_timestamp via grouping and pattern matching is the central task. It is used as a fundamentals check to test whether you pick the right aggregation function and partition boundary on the first attempt.
Break down the requirements
Step 1: Filter to the target rows
Apply the LIKE pattern match in the WHERE clause. This narrows the dataset before any grouping or aggregation.
Step 2: Aggregate with COUNT
Group by the output grain and apply COUNT() to compute the metric. The GROUP BY must match exactly what the output needs: one row per group key.
Step 3: Order the final output
Apply ORDER BY as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.
The solution
SELECT event_type, COUNT(*) AS event_count
FROM event_data
WHERE tags LIKE '%mobile%'
GROUP BY event_typeCommon follow-up questions
- What happens to your result if `event_data`.payload contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on payload.)
- How would you verify that your aggregation on `event_data`.event_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
- With millions of distinct values in `event_data`.event_id, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `event_id`.)