The 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

L5

Asked in

DoorDash

Problem

To see which account groups have the roughest time in the product, find each account status's share of events that ended badly, given as a decimal fraction of all of that group's events. Treat an event as bad when its type is error, timeout, or crash.

Some account groups hit far more errors than others. Surface the ones that break most.

Tables

users

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

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

What this really is

This is a per-group conditional rate wearing a product-analytics costume. The real question: can you compute count(bad) over count(all) inside each account_status bucket without letting integer division flatten every rate to zero? Anyone can write the CASE expression. The two things that separate candidates are casting the numerator to REAL before the divide, and knowing this rate is event-weighted, so a single noisy account can swing a whole bucket. Get the cast wrong and every dashboard cell reads 0.

The traps

Step 1: Event-weighted, not user-weighted

The rate is bad_events / all_events within each status, so one power user with 1000 events dominates that bucket. Picture two suspended accounts: A fires 999 clean events and 1 error, B fires a single error. The event-weighted rate is 2 out of 1001, roughly 0.002, but the user-weighted rate is 0.5, since half the accounts hit a bad event. The prompt asks for the share of events, so event-weighted is right here. A senior candidate still names the fork aloud, because product teams often mean the user-weighted version.

Step 2: Integer division destroys the answer

CAST(SUM(...) AS REAL) / COUNT(*) forces floating-point division. Without the cast, SQLite (and Postgres with two integer operands, and MySQL in strict mode) does integer division. SUM = 1, COUNT = 3, result = 0. Every rate below 1.0 collapses to zero and every rate at or above 1.0 collapses to 1. The query runs cleanly, the column type looks plausible, and the metric is silently wrong until someone checks a bucket by hand.

Step 3: The join sets the grain and quietly drops rows

Grouping by u.account_status means every counted event must belong to a user whose status is known, and that happens through the INNER JOIN. Two silent drops: events with a NULL user_id (system events) never match a users row, and users who fired zero events never reach the SELECT at all. Both are usually fine for an event-weighted rate, but say it aloud: the denominator is events that joined, not all events and not all accounts.

The solution

SELECT
    u.account_status,
    CAST(SUM(CASE WHEN ed.event_type IN ('error', 'timeout', 'crash') THEN 1 ELSE 0 END) AS REAL)
        / COUNT(*) AS negative_rate
FROM users u
JOIN event_data ed ON u.user_id = ed.user_id
GROUP BY u.account_status

Common follow-up questions

• Rewrite this as a user-weighted rate where each account contributes one observation regardless of event count. How does the answer change for the two-account toy example in trap 1? (Tests whether the candidate can turn an event-weighted aggregate into a user-weighted one, via a per-user subquery that flags whether each user had any bad event, then averages that flag within each status.)
• Why does the query cast the SUM to REAL before dividing by COUNT(*) instead of dividing the two integers directly? (Probes whether the candidate knows the integer-division behavior of SQLite, Postgres, and MySQL, and can name at least one alternative (multiply by 1.0, AVG with float literals, NUMERIC cast).)
• If 5 percent of events have a NULL `event_type`, do they appear in the denominator of a bucket? Do they appear in the numerator? Is that the behavior you want? (Tests three-valued logic intuition. NULL IN (...) evaluates to NULL, so the CASE returns 0 and the event is excluded from the numerator, while COUNT(*) still counts the row, so it stays in the denominator.)
• An account had zero events in the whole window. Does it show up in your result with a rate of 0, or does it vanish? What would you change if the business wanted it listed? (Probes whether the candidate recognizes that NULL `user_id` events and event-less users are both dropped by the INNER JOIN, and can reason about when a LEFT JOIN or a different grain would be needed.)
• If we restricted this to the last 30 days of events, how would you write the timestamp predicate so the planner can prune the `event_timestamp` partitions instead of scanning the whole table? (Tests SARGability and scaling awareness for the version of this query that does add a time bound: a function-free comparison on `event_timestamp` lets the planner prune the 365 daily partitions instead of scanning all 200M rows.)