Daily Error Resolution Ratio

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

L4

Asked in

Meta

Problem

For every day on which err_tracks logged at least one error more serious than informational, report how many unique services were hit, how many alerts in alert_events fired that day and were eventually cleared, and the ratio of cleared alerts to services hit as a decimal rounded to two places. The reliability team reads it as a daily health check, so an error day where nothing cleared still appears, with a zero.

Reported versus removed. The daily ratio.

Tables

err_tracks

Columns: err_id INTEGER, err_type TEXT, message TEXT, svc_name TEXT, severity TEXT, count INTEGER, first_at TEXT

alert_events

Columns: alert_id INTEGER, svc_name TEXT, severity TEXT, status TEXT, fired_at TEXT, ack_by TEXT, resolved TEXT

Expected output

Worked solution and explanation

What this really is

Strip the ops costume and this is a two-series daily alignment: one series is how many unique services threw a real error each day, the other is how many alerts fired and genuinely cleared that same day. Different tables, different filters, one shared calendar-day grain. Anyone can write the two aggregates. What separates candidates is combining them so the error days with nothing cleared stay in the report, counting services rather than error rows, and keeping the ratio a real fraction. Miss any one and the report hides exactly the backlog on-call needs to see.

Step 1: Count unique affected services per day

Bucket err_tracks by date(first_at) and take COUNT(DISTINCT svc_name). Unique matters because one service can throw the same error dozens of times in a day; COUNT(*) would report a fleet-wide meltdown from a single noisy service. Drop severity 'info' first, since an informational line is not an incident.

Step 2: Count the alerts that cleared that day

On alert_events, keep only rows where resolved IS NOT NULL, then bucket by date(fired_at) and count. The resolved timestamp is the signal, not the status text: a row can read 'resolved' while its resolved column is still null, and that alert never cleared. Each alert is a discrete incident, so a straight COUNT(*) is right here.

Step 3: Left join from the error side and take the ratio

Drive the join from affected and LEFT JOIN resolutions, so a day with no cleared alert still produces a row. COALESCE the missing count to 0, CAST it to REAL, and divide by that day's service count, looked up with a correlated scalar subquery on report_date. ROUND(..., 2) gives the two-decimal ratio the report asks for.

The solution

WITH affected AS (
    SELECT date(first_at) AS report_date,
           COUNT(DISTINCT svc_name) AS reported_services
    FROM err_tracks
    WHERE severity <> 'info'
    GROUP BY date(first_at)
),
resolutions AS (
    SELECT date(fired_at) AS event_date,
           COUNT(*) AS resolved_alerts
    FROM alert_events
    WHERE resolved IS NOT NULL
    GROUP BY date(fired_at)
)
SELECT
    a.report_date,
    a.reported_services,
    COALESCE(res.resolved_alerts, 0) AS resolved_alerts,
    ROUND(
        CAST(COALESCE(res.resolved_alerts, 0) AS REAL)
        / (SELECT a2.reported_services FROM affected a2 WHERE a2.report_date = a.report_date),
        2
    ) AS resolution_ratio
FROM affected a
LEFT JOIN resolutions res ON res.event_date = a.report_date

Inner join (wrong)

Only days that had both an error and a cleared alert appear. Quiet-but-broken days vanish and the fleet looks calmer than it is.

Left join (right)

Every error day appears; days with nothing cleared show 0 and a 0.0 ratio, surfacing exactly the backlog on-call cares about.

Common follow-up questions

• How would you also show the services that had errors but no cleared alert that day? (Pushes toward an anti-join or an aggregation of the unmatched services.)
• What changes if you need a 7-day rolling version of `resolution_ratio`? (Tests moving from per-day grain to a windowed frame over the daily series.)
• How would you keep days that had cleared alerts but no errors from being dropped? (Surfaces that the current report is intentionally error-driven, and a full outer join changes that.)