The Loudest Failures

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

Problem

Pages from the error tracker spiked through August, and the on-call engineer wants to see which services were making the noise. From err_tracks, find how many errors each service logged that month, noisiest service first.

Twelve months of errors. Which types showed up most?

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

Strip off the pager story and this is a one-table tally with a fence around it: cut one month out of a timestamp, then count rows per service. Everyone writes the GROUP BY. What separates people is the fence. Every row in err_tracks comes from the same year, so a candidate who reaches for a year filter writes a WHERE that removes nothing and reports 32 to 34 errors per service, when August really holds 5 or 6 each. The second trap is the count column sitting right there: sum it and you report occurrence weights, not how many errors were logged.

Step 1: Cut the month out of first_at

first_at is a full timestamp string, so first_at = '2026-08' matches nothing. strftime('%Y-%m', first_at) keeps only year and month, which compares cleanly to the month string. Filter before aggregating so only that month's rows reach the tally.

Step 2: Bucket by svc_name as stored

Service names are clean in this table: six values, one spelling each. Group on them directly; no normalization is needed or asked for.

Step 3: Count rows, not the count column

COUNT(*) counts logged errors, one per row. SUM(count) adds per-incident weights and also skips rows where count is NULL, and two of those fall in August.

Step 4: Sort noisiest first

ORDER BY error_count DESC. Two pairs tie on the seed, and their order within a tie is not part of the question, so a second key would add nothing.

SELECT svc_name, COUNT(*) AS error_count
FROM err_tracks
WHERE strftime('%Y-%m', first_at) = '2026-08'
GROUP BY svc_name
ORDER BY error_count DESC

Count rows (correct here)

COUNT(*) gives one per logged error inside the month: 6, 6, 5, 5. NULLs in count never matter.

Sum the weight column

SUM(count) adds per-incident weights into large, unrelated totals and ignores NULL rows, so it answers a different question.

Common follow-up questions

• Show every month of the year as its own column, one row per service. (Moves from one fenced month to a crosstab on the month string.)
• Add total occurrences next to the error count in the same row. (Holds `COUNT(*)` and `SUM(count)` side by side, NULLs included.)
• Return the noisiest `err_type` per service for that month. (A per-group top-1, and `err_type` has case variants that must be reconciled first.)