App Stability by Region

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

Some regions crash more than others.

Tables

event_data

Columns: event_id INTEGER, event_type TEXT, user_id INTEGER, event_timestamp TEXT, payload TEXT, tags TEXT, properties TEXT

Expected output

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

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

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.

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.)