Errors With Service Health

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

List every error in err_tracks beside each health reading svc_health holds for its service, showing the error's ID, type, message, service name, severity, count and first occurrence with the reading's status, latency and uptime. The on-call engineer triaging the log wants the full check history behind each error, not just the latest reading, and only for errors whose service has at least one reading on record. Types and severities go out exactly as the log recorded them, casing and all.

Error data, enriched with health context.

Tables

err_tracks

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

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this really is

This is record enrichment dressed up as on-call triage. Every error row knows which service failed, but not how that service was doing: status, latency and uptime live only in svc_health, so no query over err_tracks alone can return them. Anyone finds the shared svc_name. What trips people is that the same column name sits on both sides, so a bare svc_name in the select list raises an ambiguous-column error and the query never runs.

The second thing to notice: svc_health is a history, many checks per service, not one row per service. The statement asks for the full check history, so each error legitimately repeats once per reading. That fan-out is the answer here, not a bug to dedupe away.

Build it

Step 1: Find the column the two tables share

svc_name is the only column on both err_tracks and svc_health. It is not a key on either side: many errors and many checks point at the same service, which is exactly why rows multiply.

Step 2: Pair errors to readings and alias both sides

Alias the tables as e and s and match on e.svc_name = s.svc_name. An inner pairing also quietly honors the second half of the ask: an error whose service has no reading has nothing to pair with, so it drops out.

Step 3: Qualify every column you select

Take the seven error fields from e and the three health fields from s, passed through untouched. The statement wants types and severities as logged, so 'TYPEERROR' and 'TypeError' stay two different strings: normalizing them changes the graded values.

SELECT e.err_id, e.err_type, e.message, e.svc_name,
       e.severity, e.count, e.first_at,
       s.status, s.latency, s.uptime
FROM err_tracks e
JOIN svc_health s ON e.svc_name = s.svc_name

Unqualified

SELECT err_id, svc_name, status FROM err_tracks JOIN svc_health ON ... fails: the engine cannot tell which table's svc_name you mean.

Qualified

SELECT e.err_id, e.svc_name, s.status ... names the side for every column, so the query reads unambiguously to the engine and to the next engineer.

Common follow-up questions

• What if every error must appear, even when its service has no readings? (Tests switching to a `LEFT JOIN` and reasoning about `NULL` health columns.)
• What if on-call only wanted each service's latest reading? (Tests reducing `svc_health` to the max `checked` per `svc_name` before pairing, so errors stop fanning out.)
• How would you show how many errors each service logged on every row? (Tests a windowed count over `svc_name` versus an aggregate joined back.)