# Errors With Service Health > Error data, enriched with health context. Canonical URL: 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. ## Tables ### err_tracks - err_id INTEGER - err_type TEXT - message TEXT - svc_name TEXT - severity TEXT - count INTEGER - first_at TEXT Sample rows (10 of 200): | err_id | err_type | message | svc_name | severity | count | first_at | | --- | --- | --- | --- | --- | --- | --- | | 137 | TypeError | Cannot read property of undefined | payment-api | error | 8 | 2026-02-02 01:11:00 | | 174 | ConnectionTimeout | Connection timed out after 30s | user-svc | warning | 15 | 2026-03-03 02:22:00 | | 211 | OutOfMemory | Heap space exhausted | search-api | info | 22 | 2026-04-04 03:33:00 | | 248 | FileNotFound | File /tmp/data.csv not found | gateway | Fatal | 29 | 2026-05-05 04:44:00 | | 285 | PermissionDenied | Access denied for user root | worker | ERROR | 36 | 2026-06-06 05:55:00 | | 322 | RateLimitExceeded | Too many requests from 10.0.0.1 | auth-svc | fatal | 43 | 2026-07-07 06:06:00 | | 359 | ParseError | Malformed JSON in request body | payment-api | error | 50 | 2026-08-08 07:17:00 | | 396 | nullpointerexception | Stack overflow in recursive call | user-svc | warning | 57 | 2026-09-09 08:28:00 | | 433 | TYPEERROR | Division by zero | search-api | info | 64 | 2026-01-10 09:39:00 | | 470 | NullPointerException | Unexpected null value at line 42 | gateway | Fatal | NULL | 2026-02-11 10:50:00 | ### svc_health - check_id INTEGER - svc_name TEXT - status TEXT - latency REAL - uptime REAL - checked TEXT - region TEXT Sample rows (10 of 200): | check_id | svc_name | status | latency | uptime | checked | region | | --- | --- | --- | --- | --- | --- | --- | | 137 | payment-api | degraded | 3.6 | 97.03 | 2026-02-02 01:05:00 | us-west-2 | | 174 | user-svc | unhealthy | 6.7 | 94.06 | 2026-03-03 02:10:00 | eu-west-1 | | 211 | search-api | timeout | 9.8 | 91.09 | 2026-04-04 03:15:00 | ap-south-1 | | 248 | gateway | Healthy | 12.9 | 98.12 | 2026-05-05 04:20:00 | eu-central-1 | | 285 | notif-svc | DEGRADED | 16 | 95.15 | 2026-06-06 05:25:00 | us-east-1 | | 322 | ml-serving | healthy | 19.1 | 92.18 | 2026-07-07 06:30:00 | us-west-2 | | 359 | cache-svc | degraded | NULL | 99.21 | 2026-08-08 07:35:00 | eu-west-1 | | 396 | queue-worker | unhealthy | 25.3 | 96.24 | 2026-09-09 08:40:00 | ap-south-1 | | 433 | cron-svc | timeout | 28.4 | 93.27 | 2026-01-10 09:45:00 | eu-central-1 | | 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 | ## Expected output First 5 of 3024 rows: | err_id | err_type | message | svc_name | severity | count | first_at | status | latency | uptime | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | 137 | TypeError | Cannot read property of undefined | payment-api | error | 8 | 2026-02-02 01:11:00 | DEGRADED | 34.6 | NULL | | 174 | ConnectionTimeout | Connection timed out after 30s | user-svc | warning | 15 | 2026-03-03 02:22:00 | DEGRADED | NULL | 97.55 | | 211 | OutOfMemory | Heap space exhausted | search-api | info | 22 | 2026-04-04 03:33:00 | DEGRADED | 71.8 | 91.69 | | 248 | FileNotFound | File /tmp/data.csv not found | gateway | Fatal | 29 | 2026-05-05 04:44:00 | DEGRADED | NULL | 91.65 | | 322 | RateLimitExceeded | Too many requests from 10.0.0.1 | auth-svc | fatal | 43 | 2026-07-07 06:06:00 | DEGRADED | NULL | 91.45 | ## 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. **Every error beside every reading for its service** ```sql 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 ``` *Qualifying `e.svc_name` picks one copy of the shared column and the ambiguity disappears.* **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. > **Narrowing to the latest check answers a different question** > > Seeing the fan-out, strong candidates often reach for the most recent `checked` row per service. That would be right if the ask were current health; here it throws away the history on-call asked for and returns far fewer rows than grade. > **Thirty million checks will not broadcast** > > With one row per service, `svc_health` would be a tiny build side for a hash join. At 30M historical checks it is not, and the zipf skew on `svc_name` piles the hot services onto a few workers. Expect the output to be far larger than either input. > **Say the cardinality out loud** > > Writing the pairing is the floor. The tell is a candidate who asks whether `svc_name` is one-to-one in `svc_health` before running anything, and can say why the result has more rows than `err_tracks`. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/errors_with_service_health) - [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.