Fault Lines

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

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.

Errors by day and region. Some areas are worse than they appear.

Tables

event_data

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

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

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.

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

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

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