# Fault Lines > Errors by day and region. Some areas are worse than they appear. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L6 · Asked in: Meta ## Problem The reliability team is building a daily failure report and treats each user's age_bucket as a stand-in for region. Looking only at open, error, and crash events, give them for every day and region the number of error and crash events as a share of the number of opens. ## Tables ### event_data - event_id INTEGER - event_type TEXT - user_id INTEGER - event_timestamp TEXT - payload TEXT - tags TEXT - properties TEXT Sample rows (10 of 200): | 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"} | ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 5 of 37 rows: | event_day | region | error_rate | | --- | --- | --- | | 2022-03-24 | 35-44 | NULL | | 2022-06-11 | 45-54 | NULL | | 2022-12-09 | 25-34 | 0 | | 2023-01-10 | 35-44 | NULL | | 2023-04-25 | 45-54 | NULL | ## Worked solution and explanation ### What this really is Strip the business costume off and this is a conditional-aggregation ratio with a scoping filter hiding underneath. The skill being probed: can you build `error_and_crash_count` over `open_count`, per day and per region, while restricting the universe to exactly the three event types that matter. Anyone can write the two CASE sums. The trap is the WHERE clause. Leave it out and every day a user merely clicked a button or made a purchase becomes its own output row with a NULL rate, and your 36 rows balloon toward 161. **Schema** - event_data: event_id INTEGER PK, event_type TEXT, user_id INTEGER FK, event_timestamp TEXT - users: user_id INTEGER PK, age_bucket TEXT **Relationships** - event_data.user_id references users.user_id *`age_bucket` rides in from users and stands in for region; `event_type` and `event_timestamp` carry the metric and the day.* > **Trick to solving** > > The denominator is opens, not total events, and the row only survives if its day and region saw at least one open, error, or crash. Both halves of the ratio are SUM(CASE WHEN ...), and the WHERE filter is what controls which day/region rows exist at all. ### Building it #### Step 1: Scope the event universe first WHERE `event_type` IN ('open', 'error', 'crash') throws away clicks, purchases, signups, logins and the rest before anything is grouped. This is what stops a day where nobody opened or errored from leaking in as a phantom row with a NULL rate. #### Step 2: Pull region from users JOIN users ON `event_data`.user_id = `users.user_id` brings in `age_bucket`, which the report reads as region. The inner join also drops orphan events whose `user_id` has no match, which is what you want here. #### Step 3: Express the ratio as two conditional sums CAST(SUM(CASE WHEN `event_type` IN ('error','crash') THEN 1 ELSE 0 END) AS REAL) divided by SUM(CASE WHEN `event_type` = 'open' THEN 1 ELSE 0 END). The CAST forces real division, so 3 crashes over 10 opens reads 0.3 instead of integer 0. When a group has errors or crashes but zero opens, the divide-by-zero yields NULL, which is exactly what the preview shows for those rows. #### Step 4: Collapse to one row per day and region GROUP BY `event_day`, region produces one row per combination. `event_day` is the first ten characters of the timestamp string, substr(`event_timestamp`, 1, 10). **Per-day, per-region failure ratio** ```sql SELECT substr(ed.event_timestamp, 1, 10) AS event_day, u.age_bucket AS region, CAST(SUM(CASE WHEN ed.event_type IN ('error', 'crash') THEN 1 ELSE 0 END) AS REAL) / SUM(CASE WHEN ed.event_type = 'open' THEN 1 ELSE 0 END) AS error_rate FROM event_data ed JOIN users u ON ed.user_id = u.user_id WHERE ed.event_type IN ('open', 'error', 'crash') GROUP BY event_day, region ``` *The WHERE filter and the opens-only denominator are the two load-bearing choices.* **Without the WHERE filter** Every day/region with any activity becomes a row. Days of pure clicks and purchases have zero opens and zero errors, so the ratio is 0/0 = NULL, and the result swells to 161 rows of mostly noise. **With WHERE `event_type` IN (...)** Only days/regions that actually emitted an open, error, or crash survive. The result is the 36 rows the report is meant to show, with NULL reserved for groups that errored but never opened. > **Common pitfall** > > Two ways candidates blow this. First, using COUNT(*) or total events as the denominator instead of opens only, which silently changes what the rate means. Second, dropping the WHERE filter and trusting NULLIF to paper over the zero denominators: the arithmetic still runs, but every day/region with activity yet no open/error/crash leaks in, and your row count quadruples. > **Interviewers watch for** > > The tell is whether you restrict the event universe before aggregating. A junior computes the ratio over everything and leans on NULLIF to hide the zero denominators. A senior scopes to the three relevant types up front so the output contains only days that carried real reliability signal. > **Performance insight** > > `event_data` is 200M rows partitioned by timestamp. Pushing WHERE `event_type` IN ('open','error','crash') down before the join cuts the probe side dramatically, since errors and crashes are a thin slice of a zipf-skewed event mix. With users keyed on `user_id`, the join is a 15M-row hash build that fits in memory, and the conditional sums fold into a single grouped pass. ## Common follow-up questions - How would you surface only the day/region pairs whose error rate clears a threshold? _(Tests HAVING on a computed conditional-aggregation expression.)_ - Some events have a NULL `user_id` and some users have a NULL `age_bucket`. What happens to those, and is that what you want? _(Tests reasoning about inner-join drops and NULL group keys.)_ - How would you show each region's error rate this week next to last week's? _(Tests windowing or a self-join across date ranges.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/fault_lines) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.