# The Fault Lines > Some account groups hit far more errors than others. Surface the ones that break most. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L5 ## Problem To see which account groups have the roughest time in the product, find each account status's share of events that ended badly, worst first. Treat an event as bad when its type is error, timeout, or crash. ## 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. Account A fires 999 clean events and 1 error. Account B fires a single event, an error. The event-weighted rate for that bucket is 2 out of 1001, roughly 0.002. The user-weighted rate would be 0.5, because half the accounts hit a bad event. The prompt asks for the share of events, so event-weighted is correct here. A senior candidate still names the fork out loud, because product teams often mean the user-weighted version when they say negative outcome rate. #### 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 **Conditional rate per account status** ```sql 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 ORDER BY negative_rate DESC, u.account_status ``` > **The join is the cost driver** > > `event_data` is 200M rows and `users` is 15M. With no date predicate to prune on, the join has to reach every event and pair it with its user. The planner wants a hash join keyed on user_id, and the reads that matter are just `(user_id, event_type)` on the events side and `(user_id, account_status)` on the users side, so a covering index or a column-pruned scan keeps this off the full 38GB table body. The aggregation itself is cheap: only four distinct account_status values, so the GROUP BY collapses to a tiny hash table. > **What a senior candidate raises** > > Two clarifications worth raising before writing SQL. (a) Weighting: event-weighted or user-weighted, because the answers can differ by orders of magnitude. (b) System events: an event with a NULL user_id drops out entirely through the INNER JOIN, so it is in neither numerator nor denominator. That is usually what you want, since it has no account status to bucket into, but the interviewer wants to hear you notice it rather than discover it in production. > **The integer-division trap, two ways** > > `SUM(CASE WHEN ed.event_type IN ('error','timeout','crash') THEN 1 ELSE 0 END) / COUNT(*)` returns 0 in SQLite, Postgres, and MySQL when both sides are integers. The same bug hides inside `COUNT(CASE WHEN ed.event_type IN ('error','timeout','crash') THEN 1 END) / COUNT(*)`. The fix is one of: cast the numerator to REAL or NUMERIC, multiply it by `1.0`, or use the `AVG(CASE ... THEN 1.0 ELSE 0.0 END)` form. Pick one convention and hold it across the codebase, or every new rate column is a fresh bug waiting to ship. > **AVG with float literals is shorter** > > `AVG(CASE WHEN ed.event_type IN ('error','timeout','crash') THEN 1.0 ELSE 0.0 END) AS negative_rate` says the same thing in one expression, and the `1.0` literal forces floating point throughout, so there is no CAST to forget. It is exactly equivalent to the SUM over COUNT form on the same condition, and many teams prefer it for readability in dashboards full of rate columns. --- ## 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 transform an event-weighted aggregate into a user-weighted one, typically 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_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.