Most Frequent Error Types

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

L3

Asked in

Forbes

Problem

On-call is triaging err_tracks, an error log where each report names an error type and records how many times that error fired. Surface the five error types with the most total firings, and for each show how many reports arrived alongside that firing total, biggest firing total first.

The errors that keep coming back.

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

The mental model

This looks like a plain frequency count, and that is the trap. The column literally named count is not the number of reports, it is how many times a single report saw the error fire, so the two tallies this question wants are easy to swap. COUNT(*) grouped by err_type gives the report tally; SUM(count) gives the true firing total. On this log the report tallies come out dead flat (20 per type), so they cannot rank anything: only SUM(count) spreads the types apart, and the ORDER BY total_occurrences DESC plus LIMIT 5 is what actually picks the five worst offenders. Rank by the flat tally, or drop the sort, and a different, arbitrary five come back. There is a second, quieter catch: err_type stores case variants ('TypeError' with 'TYPEERROR', 'NullPointerException' with 'nullpointerexception') as distinct values, so reaching for LOWER(err_type) merges pairs the grader keeps apart and changes both the totals and which five survive.

The traps

Step 1: Two tallies, not one

The output wants two different numbers per type, and they are computed differently. COUNT(*) counts the report rows in each group; SUM(count) adds up the per-report firing counts. Emit COUNT(*) AS report_count and SUM(count) AS total_occurrences side by side. Using SUM(count) where the report tally is meant, or COUNT(*) where the firing total is meant, is the whole trap: the two columns answer different questions.

Step 2: Do not fold the casing

err_type holds 'TypeError' and 'TYPEERROR', 'NullPointerException' and 'nullpointerexception' as distinct stored values. Reaching for LOWER(err_type) to tidy them merges each pair into one bucket and quietly changes both the totals and which types clear the top-five cutoff. The grader keeps the types separate, so group on the raw column.

Step 3: Rank by the firing total, then keep the top five

The report tally is a flat 20 for every type, so it cannot rank anything: the ranking has to run on SUM(count), whose firing totals spread widely. ORDER BY total_occurrences DESC then LIMIT 5 is load-bearing here, not decoration: it decides which five types appear. Drop the sort and the LIMIT keeps five in group order, the wrong five. Because every type's firing total is distinct on this data, the cutoff is unambiguous and the ordering is total, so no tiebreaker is needed.

The solution

SELECT
    err_type,
    COUNT(*) AS report_count,
    SUM(count) AS total_occurrences
FROM err_tracks
GROUP BY err_type
ORDER BY total_occurrences DESC
LIMIT 5

Common follow-up questions

• How would the top five change if you ordered by `report_count` instead of `total_occurrences`? (Forces the row-vs-firing distinction: `report_count` is flat for every type here, so ranking by it would be arbitrary.)
• If you normalized `err_type` with `LOWER()`, how would the result change, and why is that wrong here? (Tests the case-fold trap: `LOWER()` merges 'TypeError' with 'TYPEERROR' and collapses distinct types out of the result.)
• How would you return every error type tied at the fifth-place firing total, not just the first five rows? (Probes window-function fluency: `LIMIT` drops ties at the cutoff, while `DENSE_RANK` keeps every type sharing the boundary total.)
• Production wants this broken down by hour of `first_at`. What changes in the query? (Operationalizes the metric for a live incident dashboard rather than a one-shot postmortem.)
• How would you rewrite this to be partition-pruning friendly on `first_at`? (Connects query shape to physical layout: partition pruning needs a predicate on the partition key the planner can evaluate at plan time.)