Top Error-Service Pair

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

During an incident postmortem, you need the error-and-service pairing behind the most resolved incidents. Pair each error type with its service name from the error tracker using a hyphen, then count the separate resolved alerts each pairing accounts for, where an alert counts as resolved only if its resolved field is filled in. Return the top pairings including ties, listed alphabetically by the pairing.

Which error-service pair triggered the most resolved incidents

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 problem really is

This is a ranking-with-ties over a fan-out join, wearing a postmortem costume. The real question: which err_type-svc_name pairing owns the most resolved alerts, when the only shared key between the error tracker and the alert log is svc_name? That join multiplies every alert by the number of error rows on its service, so COUNT(*) counts the fan-out, not incidents. And resolved is a nullable timestamp, so skip the IS NOT NULL test and you fold every still-firing alert into the total. Get either wrong and the leaderboard is fiction.

Break down the requirements

Step 1: Confirm the join key

Join err_tracks to alert_events on svc_name, the only key the two tables share. There is no err_id on the alert side, so the grain of the join is one error row against every alert for that service.

Step 2: Filter to resolved alerts with the NULL-aware test

resolved holds the resolution timestamp and is NULL for alerts that never closed. a.resolved IS NOT NULL scopes the count to resolved incidents. This filter is load-bearing: about one alert in five is still open, and counting those turns the total into alert volume rather than resolved incidents.

Step 3: Build the composite identifier

Most engines do not allow COUNT(DISTINCT col1, col2), so build the key with e.err_type || '-' || e.svc_name AS reporter_identity and group on it. Pick a delimiter that cannot appear in either column.

Step 4: Count distinct resolved incidents per pairing

COUNT(DISTINCT a.alert_id) collapses the fan-out: the svc_name join repeats each alert once per matching error row, so a plain COUNT(*) multiplies each incident by the number of error types on that service. The DISTINCT counts each resolved incident exactly once.

Step 5: Rank with ties, order alphabetically

RANK() OVER (ORDER BY COUNT(DISTINCT a.alert_id) DESC) then filter rnk = 1. Use RANK (not ROW_NUMBER) so tied leaders all surface. A final ORDER BY reporter_identity gives the alphabetical sequence, and since each pairing appears once the order is total.

The solution

SELECT reporter_identity, resolved_incidents
FROM (
  SELECT e.err_type || '-' || e.svc_name AS reporter_identity,
         COUNT(DISTINCT a.alert_id) AS resolved_incidents,
         RANK() OVER (ORDER BY COUNT(DISTINCT a.alert_id) DESC) AS rnk
  FROM err_tracks e
  JOIN alert_events a ON e.svc_name = a.svc_name
  WHERE a.resolved IS NOT NULL
  GROUP BY e.err_type || '-' || e.svc_name
) ranked
WHERE rnk = 1
ORDER BY reporter_identity

Common follow-up questions

Common follow-up questions

• Why `RANK()` instead of `DENSE_RANK()` or `ROW_NUMBER()` here? (Probes tie handling: `RANK` and `DENSE_RANK` both keep ties at the top; `ROW_NUMBER` breaks them arbitrarily. For `rnk = 1` either RANK variant works, but the distinction matters when filtering a top-N band.)
• How would you produce the pairing without string concatenation? (Tests `GROUP BY err_type, svc_name` with the two columns projected separately, sidestepping delimiter collisions entirely.)
• What changes if you must also require the alert fired after the error's `first_at`? (Probes inequality joins and whether the `COUNT(DISTINCT a.alert_id)` semantics stay correct when a time window can match an alert to several error rows.)
• How would you make this incremental for a streaming postmortem dashboard? (Tests whether the candidate can decompose `COUNT(DISTINCT alert_id)` into mergeable per-pairing sketches (HyperLogLog) for a rolling dashboard.)