# Daily Error Resolution Ratio

> Reported versus removed. The daily ratio.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each date, count unique TypeError errors flagged and how many alerts were resolved on the same day. For dates with reports but no resolutions, show zero. Include the date, reported count, resolved count, and ratio.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a LEFT JOIN between two tables on a date dimension with conditional counting. The requirement to show zero for unresolved dates probes COALESCE usage.

---

### Break down the requirements

#### Step 1: Count daily TypeError errors

From `err_tracks`, filter `err_type = 'TypeError'`, group by `first_at::DATE`, count distinct `err_id`.

#### Step 2: Count daily resolved alerts

From `alert_events`, filter resolved, group by date, count distinct.

#### Step 3: Left join and compute ratio

LEFT JOIN errors to resolutions on date. Use COALESCE for zero-fill on unmatched dates.

---

### The solution

**Left join with date-level ratio**

```sql
WITH daily_errors AS (
    SELECT first_at::DATE AS day, COUNT(DISTINCT err_id) AS reported
    FROM err_tracks
    WHERE err_type = 'TypeError'
    GROUP BY first_at::DATE
),
daily_resolved AS (
    SELECT fired_at::DATE AS day, COUNT(DISTINCT alert_id) AS resolved
    FROM alert_events
    WHERE resolved = true
    GROUP BY fired_at::DATE
)
SELECT
    e.day,
    e.reported,
    COALESCE(r.resolved, 0) AS resolved,
    CASE WHEN e.reported > 0
         THEN ROUND(COALESCE(r.resolved, 0)::NUMERIC / e.reported, 2)
         ELSE 0 END AS ratio
FROM daily_errors e
LEFT JOIN daily_resolved r ON e.day = r.day
ORDER BY e.day
```

> **Cost Analysis**
>
> Two scans: 25M err_tracks and 15M alert_events, each aggregated to ~365 daily rows. The LEFT JOIN operates on these small sets. The initial scans dominate.

> **Interviewers Watch For**
>
> Whether the candidate uses LEFT JOIN (correct) to preserve dates with errors but no resolutions. An INNER JOIN would drop those dates entirely.

> **Common Pitfall**
>
> Dividing by zero when `reported` is 0 would cause an error. The CASE guard or NULLIF in the denominator is essential.

---

## Common follow-up questions

- How would you also include dates with resolutions but no errors? _(Use FULL OUTER JOIN instead of LEFT JOIN.)_
- What if you needed to match errors to alerts by service name, not just date? _(Add svc_name to the GROUP BY and JOIN condition.)_
- How would you compute a rolling 7-day resolution ratio? _(Use window functions with a 7-day frame on the daily data.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/daily_error_resolution_ratio)
- [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.