# The Failure Report

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

Canonical URL: <https://datadriven.io/problems/error_rate_by_region>

Domain: SQL · Difficulty: medium · Seniority: L6

## Problem

Each event in event_data has a type such as open, error, or crash. Enrich the event_data table with the users table to get each user's age_bucket as a proxy for region. Then compute the ratio of error events (types error and crash) to total open events, broken down by day and region.

## Worked solution and explanation

### Why this problem exists in real interviews

Error rate by region tests JOIN plus conditional aggregation. The interviewer checks whether you compute a rate (errors divided by total events) rather than a raw count, and correctly join for region data.

---

### Break down the requirements

#### Step 1: Join events to users for region

`JOIN users ON event_data.user_id = users.user_id` maps each event to a region.

#### Step 2: Compute error rate per region

Use `AVG(CASE WHEN event_type = 'error' THEN 1.0 ELSE 0.0 END) * 100` for the error rate percentage.

#### Step 3: Group by region

`GROUP BY age_bucket` (used as the region proxy) and order by error rate descending.

---

### The solution

**Conditional aggregation for per-region error rate**

```sql
SELECT u.age_bucket AS region,
       COUNT(*) AS total_events,
       SUM(CASE WHEN e.event_type = 'error' THEN 1 ELSE 0 END) AS error_count,
       ROUND(AVG(CASE WHEN e.event_type = 'error' THEN 1.0 ELSE 0.0 END) * 100, 2) AS error_rate_pct
FROM event_data e
JOIN users u ON e.user_id = u.user_id
GROUP BY u.age_bucket
ORDER BY error_rate_pct DESC
```

> **Cost Analysis**
>
> The join is the most expensive step. An index on `users(user_id)` makes the lookup efficient.

> **Interviewers Watch For**
>
> The key distinction is rate vs. count. A region with high error count but high total volume may have a low error rate.

> **Common Pitfall**
>
> Using `COUNT(CASE WHEN ... THEN 1 ELSE 0 END)` counts all rows (zeros included). Use SUM or COUNT without ELSE.

---

## Common follow-up questions

- How would you flag regions with error rate above a threshold? _(Tests HAVING on the computed rate.)_
- What if some users have no region set? _(Tests NULL handling in GROUP BY.)_
- How would you compare this week to last week? _(Tests self-join or LAG with date partitioning.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/error_rate_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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.