# App Stability by Region > Some regions crash more than others. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 · Asked in: Visa ## Problem The mobile team is weighing the next rollout and wants to see where the app keeps falling over. Looking only at app opens and crashes, count each for every tag set and event timestamp, and report the crash rate as crashes divided by opens, rounded to four decimal places. ## 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"} | ## Expected output First 5 of 26 rows: | event_timestamp | tags | num_opens | num_crashes | crash_rate | | --- | --- | --- | --- | --- | | 2022-03-24 03:57:00 | ["mobile", "android", "free", "campaign_a", "referral"] | 0 | 1 | NULL | | 2022-12-09 12:12:00 | ["ios", "premium"] | 1 | 0 | 0 | | 2023-01-10 13:19:00 | ["android", "free", "campaign_a"] | 0 | 1 | NULL | | 2023-10-23 22:34:00 | ["premium", "new_user", "organic", "beta"] | 1 | 0 | 0 | | 2024-11-24 23:41:00 | ["free", "campaign_a", "referral", "experiment_v1", "desktop"] | 0 | 1 | NULL | ## Worked solution and explanation ### Why this problem exists in real interviews This is conditional aggregation wearing a reliability costume. The real question: can you pivot two event types into side-by-side counts and divide them in one pass, without the integer-division trap eating your ratio? Anyone can filter to opens and crashes. What separates candidates is forcing float division and deciding what a bucket with zero opens should return. Miss the first and a real 17 percent crash rate rounds to 0; miss the second and the whole query dies on the first tag set that never opened. --- ### Break down the requirements #### Step 1: Filter to the two event types `WHERE event_type IN ('open', 'crash')` strips the rest before aggregation. Doing it in `WHERE` (not inside the CASE) shrinks what the GROUP BY hashes, especially given the 500M-row scale. #### Step 2: Pivot via conditional SUM Two `SUM(CASE WHEN event_type = 'open' THEN 1 ELSE 0 END)` expressions turn rows into wide counts. `COUNT(CASE WHEN ... THEN 1 END)` works too; both ignore the other event type cleanly. #### Step 3: Group by the bucket keys `GROUP BY event_timestamp, tags` matches the grain in the output preview: each distinct (timestamp, tag set) pair is one row. Keep the timestamp raw and do not truncate it to a calendar day, because the preview shows full timestamps with their time components. #### Step 4: Compute the ratio safely `crashes * 1.0 / opens` forces float division, then `ROUND(..., 4)`. If a bucket can have zero opens, this divides by zero; wrap the denominator in `NULLIF(..., 0)` to return NULL instead of erroring. --- ### The solution **CONDITIONAL-AGG PIVOT FOR CRASH RATE** ```sql SELECT event_timestamp, tags, SUM(CASE WHEN event_type = 'open' THEN 1 ELSE 0 END) AS num_opens, SUM(CASE WHEN event_type = 'crash' THEN 1 ELSE 0 END) AS num_crashes, ROUND( SUM(CASE WHEN event_type = 'crash' THEN 1 ELSE 0 END) * 1.0 / SUM(CASE WHEN event_type = 'open' THEN 1 ELSE 0 END), 4 ) AS crash_rate FROM event_data WHERE event_type IN ('open', 'crash') GROUP BY event_timestamp, tags ``` > **Cost Analysis** > > 500M rows, partitioned by `event_timestamp`. With no date predicate the planner does a full partition scan plus a hash aggregate keyed on (timestamp, tags). Add `WHERE event_timestamp >= DATE '2024-01-01'` (or whatever window the rollout cares about) and you get partition pruning, which is the only thing that makes this query interactive. The `event_type IN (...)` filter happens after partition selection, so it does not save scan cost on its own. > **Interviewers Watch For** > > Whether you confirm two things out loud before typing: that the grouping key is the raw `event_timestamp` and `tags` pair (the output preview settles it, since it shows full timestamps), and that the crash rate's denominator is opens only, not every event. Candidates who count all events in the denominator quietly report a deflated, wrong rate. > **Common Pitfall** > > Writing `SUM(crashes) / SUM(opens)` without `* 1.0` returns an integer in most engines, so a 17 percent crash rate rounds to 0. The canonical query multiplies by `1.0` for exactly this reason. Same family of bug: forgetting `NULLIF` and killing the whole query on the first bucket where `num_opens = 0`. **Long format: GROUP BY `event_timestamp`, tags, `event_type`** One row per event type per bucket. Opens and crashes land on separate rows, so a consumer has to pivot before it can divide. More rows, more downstream work. **Wide format: conditional SUM** Two `SUM(CASE WHEN ...)` expressions put `num_opens` and `num_crashes` side by side in one row, so `crash_rate` is computable inline. This is the shape the output preview asks for. > **Key Insight** > > The query returns the components (opens, crashes, ratio) rather than just the ratio. That's deliberate: a downstream consumer can re-aggregate (sum opens and crashes across regions, then divide) without losing precision. Pre-dividing per bucket and then averaging gives the wrong global rate. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you change the query to handle regions with zero opens in a given bucket? _(Probes `NULLIF` and the divide-by-zero failure mode that the canonical answer ignores.)_ - If the mobile team wants a global crash rate across all regions, can they just average the per-region `crash_rate` column? _(Tests whether the writer understands ratio-of-sums vs sum-of-ratios. The correct answer: no, they must re-sum opens and crashes.)_ - The team asks for a 7-day rolling crash rate per region. How does the query change? _(Pushes toward window functions over the daily aggregate, or a self-join on a date range. Tests window-frame fluency.)_ - `tags` is actually a delimited string like 'us-east,ios,prod'. How do you group by just the region? _(Tests string parsing and whether the writer pushes back on schema before pivoting on the raw column.)_ - How would you make this query cheap enough to run hourly? _(Targets partition pruning, pre-aggregated daily rollup tables, and whether the writer reaches for an incremental materialized view instead of re-scanning 500M rows.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/app_stability_by_region) - [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.