The Usual Suspects

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Vesta Innovations

Problem

Timed-out health checks keep paging the on-call engineer, and she wants to know which service is behind them in each cloud region. From svc_health, list every service that logged the most timeouts in its region, co-leaders included, with that count, the calendar month it first timed out, and how many services in the region logged a timeout at all.

Same services, same checks, same problems.

Tables

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

Strip the monitoring costume off and this is a per-region argmax with ties: for each region, which service logged the most timed-out checks. The skill it probes is scoping the maximum to the group instead of the whole table. Anyone can count timeouts per service. The tell is whether your MAX is correlated to the region or applied globally. A global MAX compares every region against the single top count of 4, so only the two regions that reach it come back and the other three, each with co-leaders at 3, vanish. The two extra asks, the month each leader first timed out and how many services in the region timed out at all, ride for free on the same grouped set.

Working through it

Step 1: Filter to the timed-out checks

Keep only the timed-out checks with WHERE status = 'timeout'. The status column has mixed casing on the other outcomes ('Healthy'/'healthy', 'DEGRADED'/'degraded'), which is a distractor here: 'timeout' is always written in lower case, so a plain equality is exact. Putting the predicate in WHERE also lets it run before aggregation, cutting the scan down first.

Step 2: Count timeouts per service within each region

GROUP BY region, svc_name with COUNT(*) AS timeout_count gives one row per service per region. In the same pass, strftime('%Y-%m', MIN(checked)) records the calendar month that service first timed out. Materialize this in a CTE named regional_counts so you can lean on it three times: the rows to keep, the source of each region's maximum, and the self-join peer set.

Step 3: Correlate the maximum to the region

The comparison must be scoped to the same region: WHERE timeout_count = (SELECT MAX(timeout_count) FROM regional_counts rc2 WHERE rc2.region = rc.region). The correlation on rc2.region = rc.region is the whole game. Drop it and you compare every region against one global maximum. Because it is equality, every service tied at a region's top count survives, so co-leaders both come back.

Step 4: Count the region's other offenders with a self-join

JOIN regional_counts peer ON peer.region = rc.region fans each surviving leader out to every service in its region, and COUNT(peer.svc_name) after the GROUP BY collapses that back into how many services in the region logged any timeout. The self-join reads the already-tiny CTE, not the base table, so it costs almost nothing.

The solution

WITH regional_counts AS (
  SELECT
    region,
    svc_name,
    COUNT(*) AS timeout_count,
    strftime('%Y-%m', MIN(checked)) AS first_timeout_month
  FROM svc_health
  WHERE status = 'timeout'
  GROUP BY region, svc_name
)
SELECT
  rc.region,
  rc.svc_name,
  rc.timeout_count,
  rc.first_timeout_month,
  COUNT(peer.svc_name) AS services_with_timeouts
FROM regional_counts rc
JOIN regional_counts peer
  ON peer.region = rc.region
WHERE rc.timeout_count = (
  SELECT MAX(rc2.timeout_count)
  FROM regional_counts rc2
  WHERE rc2.region = rc.region
)
GROUP BY rc.region, rc.svc_name, rc.timeout_count, rc.first_timeout_month

Common follow-up questions

• How would you rewrite this per-region argmax with a window function instead of a correlated `MAX` subquery, and when would you prefer one? (Tests the window-function alternative. `RANK() OVER (PARTITION BY region ORDER BY timeout_count DESC)` with `WHERE rnk = 1` is equivalent and preserves ties, but the candidate should note it reads the CTE one extra time versus the correlated subquery.)
• What changes if the on-call team wanted the worst offender for any failed status, not just timeouts? (Tests whether the candidate handles the casing trap correctly if the metric broadened. They should reach for `LOWER(status)` to fold 'Healthy'/'healthy' and 'DEGRADED'/'degraded' together.)
• How would you also return, across all regions, the single service with the highest regional timeout count? (Tests a global rollup on top of the per-region result. The candidate should wrap the regional leaders and take the overall maximum, or reason about whether a single global scalar MAX over `regional_counts` answers it directly.)