Services With Most Checks in 2025

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

Problem

For each service in svc_health, which year had the most checks come back anything other than healthy, how many were there, and in which month did that year's last troubled check land? Our status values were logged with inconsistent casing, so 'Healthy' and 'healthy' are the same outcome, and when two years tie for a service's worst we want both.

Last year's most-checked services.

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

This is a per-group argmax wearing an incident-review costume. Everyone can count troubled checks per service per year. What separates candidates is getting the year back out: MAX(check_count) tells you how bad the worst year was, never which year it was or when its last trouble landed. The second trap is upstream: status was logged in mixed case, so a filter on the raw text counts every 'Healthy' row as trouble and quietly inflates the very counts you are comparing.

The trap

Looks right

WHERE status <> 'healthy' keeps 'Healthy' rows, so they are counted as trouble and can move a service's worst year.

Is right

WHERE LOWER(status) <> 'healthy' folds every spelling into one outcome before the filter runs.

Building it

Step 1: Filter the troubled checks, case-blind

LOWER(status) <> 'healthy' keeps degraded, unhealthy and timeout checks in any casing. A null latency or uptime does not matter: the row is still a check.

Step 2: Bucket by year in trouble_counts

Group on svc_name and DATE_TRUNC('year', checked). In the same pass take COUNT(*) for the size of the bucket and MAX(checked) for its last troubled check, so the detail you need later rides along with the bucket instead of costing a second scan.

Step 3: Find each service's own peak in worst_year

Aggregate trouble_counts again by svc_name with MAX(check_count). This is the number, with the year stripped off.

Step 4: Join the buckets back to their peak

Join on svc_name and check_count = max_count. The join recovers the year, and because it is an equality, two years sharing the peak both survive. Only now turn the bucket into readable values: EXTRACT(YEAR FROM year_start) for the year and TO_CHAR(last_trouble_at, 'YYYY-MM') for the month.

WITH trouble_counts AS (
  SELECT svc_name,
         DATE_TRUNC('year', checked) AS year_start,
         COUNT(*) AS check_count,
         MAX(checked) AS last_trouble_at
  FROM svc_health
  WHERE LOWER(status) <> 'healthy'
  GROUP BY svc_name, DATE_TRUNC('year', checked)
),
worst_year AS (
  SELECT svc_name, MAX(check_count) AS max_count
  FROM trouble_counts
  GROUP BY svc_name
)
SELECT tc.svc_name,
       EXTRACT(YEAR FROM tc.year_start) AS check_year,
       tc.check_count,
       TO_CHAR(tc.last_trouble_at, 'YYYY-MM') AS last_trouble_month
FROM trouble_counts tc
JOIN worst_year wy
  ON tc.svc_name = wy.svc_name
 AND tc.check_count = wy.max_count

Common follow-up questions

• How would you return only one year per service, picking the most recent when two years tie? (Tests adding a deterministic tie rule on top of the peak.)
• What changes if the worst period should be a calendar month instead of a year? (Swapping `DATE_TRUNC('year', checked)` for a month bucket and checking the output labels still make sense.)
• How would you show each worst year's share of troubled checks out of all its checks? (Needs a second count over every check, not just the filtered ones.)