The Ones We Watch

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

Problem

The SRE team's error review keeps surfacing noisy services that nobody has an alert on, so it now covers only services with at least one alert in alert_events. Total the error occurrences each of those services has logged in err_tracks and list them from noisiest to quietest.

Only the services still under watch count. Rank them by where the errors gather.

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

Strip the costume

Under the observability story this is a semi-join feeding a grouped SUM. The skill is to scope err_tracks to the services that have an alert in alert_events, then total each one's occurrences without letting the second table multiply anything. Anyone can sum a column. What separates candidates is reading 'has an alert' as a yes-or-no membership test instead of a join, and summing the count column instead of counting rows. Join instead and every total comes back about 25 times too big. Skip the scope and 'worker' walks into the standings.

The scope is a membership test

A service is in scope if alert_events holds at least one row for it. That is a question of existence, not a row multiplier, so express it as a membership test (IN a subquery, or EXISTS). The test exists because of 'worker'. It logs 30,777 occurrences in err_tracks but has no alert anywhere. Without the scope it slots in between 'user-svc' and 'auth-svc' and looks exactly like a watched service.

Total occurrences, not rows

Each err_tracks row is one error type with a count of how many times it fired, so 'occurrences' means SUM(count), not the number of rows. COUNT(*) flattens every watched service to 33 or 34 rows, and the ordering turns into noise. Eighteen rows have a NULL count, six of them on 'gateway'. SUM skips them, so 'gateway' lands at 27,643 instead of failing or zeroing out.

SELECT e.svc_name AS svc_name,
       SUM(e.count) AS total_errors
FROM err_tracks e
WHERE e.svc_name IN (
  SELECT a.svc_name FROM alert_events a
)
GROUP BY e.svc_name
ORDER BY total_errors DESC;

Step 1: Scope to watched services

Build the in-scope set: every svc_name in alert_events, which is 'payment-api', 'user-svc', 'search-api', 'gateway', 'notif-svc', 'db-primary', 'cache-01' and 'auth-svc'. 'worker' appears only in err_tracks, and dropping it is the whole point of the scope.

Step 2: Total the occurrences

Group the surviving err_tracks rows by svc_name and sum count: 'payment-api' 32,198, 'search-api' 31,379, 'user-svc' 31,308, 'auth-svc' 30,771, 'gateway' 27,643. 'notif-svc', 'db-primary' and 'cache-01' are in scope but log no errors, so they never appear.

Step 3: Order most to least

Sort by total_errors descending so the noisiest watched service leads. The five totals are all different, so no tie rule is needed and 'payment-api' sits on top.

Naive: join, then sum

Joining alert_events repeats each error row once per alert, so every total is inflated about 25 times over. Dropping the scope instead lets 'worker' and its 30,777 occurrences into the list. Either way the numbers are wrong.

Correct: membership test, then sum

IN (SELECT svc_name FROM alert_events) scopes without duplicating rows, SUM(count) totals real occurrences and skips the NULLs, and 'worker' stays out.

Common follow-up questions

• Now return only the single busiest watched service, but if several tie for the top total, return all of them. How does the query change? (Pushes them from a plain sort toward a ranking function and away from a blind LIMIT.)
• Include watched services that logged zero errors, showing them with a total of 0. What do you change? (Tests an outer join from the scope set with COALESCE instead of an inner membership filter.)
• Break the totals out by `severity` while normalizing casing like 'ERROR' and 'error' into one bucket. What do you add? (Extends the grouping and forces case normalization on a dirty column.)