Services With Most Error Occurrences

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

ESPN

Problem

The reliability team is auditing the first half of the year's error logs, where each row records how many times a given error fired. For each service, surface its single highest occurrence count, biggest spike first.

The noisiest services.

Tables

err_tracks

Columns: err_id INTEGER, err_type TEXT, message TEXT, svc_name TEXT, severity TEXT, count INTEGER, first_at TEXT

Expected output

Worked solution and explanation

What this is really asking

Beneath the incident-review framing this is a per-service MAX, not a sum. The count column already stores how many times each error fired, so a service's 'peak' is the single largest count it logged, MAX(count). The trap is reading 'error occurrences' as a total and writing SUM(count) or COUNT(*): both answer a different question and re-sort the board. One row also logs a null count; MAX steps over it for free, while a hand-rolled peak or a SUM would have to reckon with it.

Scope, collapse, order

Step 1: Keep the first half of the year

CAST(strftime('%m', first_at) AS INTEGER) <= 6 pulls the month out of the first_at timestamp and keeps January through June. Do it in WHERE so those rows are gone before the grouping runs, not filtered after the fact. This window matters: several services peak later in the year, so scoping it changes their answer.

Step 2: Collapse to one row per service

GROUP BY svc_name gives one row per service, and MAX(count) reads that service's single busiest spike. Group on svc_name exactly as stored: the messy casing in severity is a decoy, and severity never enters the answer.

Step 3: Order the board

ORDER BY max_error_count DESC puts the loudest service on top. Over this window every service's peak is a distinct number, so the single sort key already yields a total order: two correct answers cannot disagree on row placement, and no tiebreak is needed.

The solution

SELECT svc_name, MAX(count) AS max_error_count
FROM err_tracks
WHERE CAST(strftime('%m', first_at) AS INTEGER) <= 6
GROUP BY svc_name
ORDER BY max_error_count DESC

Common follow-up questions

• How would the query change to report each service's peak within every month, instead of one peak across the half-year? (Tests moving from a single window to a compound date grain with a second GROUP BY key.)
• If `count` could be negative for a correction row, does `MAX(count)` still give the answer you want? (Probes whether the candidate understands MAX semantics against a domain where a negative correction row is possible.)
• What breaks in `strftime('%m', first_at)` if `first_at` were stored as a UNIX epoch integer instead of a text timestamp? (Tests portability of the date extraction if the storage type changes.)