Error Hall of Fame

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

City and County of San Francisco

Problem

The on-call team's weekly error review lists TypeError and TYPEERROR as two separate problems, which splits the biggest offender in half. Rebuild the review from the err_tracks table: every error type with how many times it occurred, most frequent first.

The year's worst error categories.

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

This is a rollup read dressed up as an error leaderboard. err_tracks does not hold one row per error event: each row is a tracked error with its own tally in count, and the same error is spelled more than one way in err_type. Anyone can write a grouped count. What separates candidates is reading the row before writing the query. If you count rows, every raw spelling ties at 20. If you group the raw string, TypeError shows up twice, so the real leader, 'typeerror' at 39,489, never appears as one line.

Two ways the obvious query lies

Step 1: Decide what one row means

Look at the sample before you write the aggregate. count holds 8, 15, 22: those are occurrences the tracker already tallied, so a type's total is SUM(count). COUNT(*) answers a different question (how many tracked errors exist), and on this seed it returns 20 for every spelling.

Step 2: Fold the spelling before you group

'TypeError' and 'TYPEERROR' are one error. Group on LOWER(err_type) and alias it error_type, so the bucket and the label are the same expression. Grouping the raw column keeps 10 rows where the review needs 8.

Step 3: Sort by the total, biggest first

ORDER BY occurrences DESC puts triage's first stop on top. The eight totals all differ on this data, so no tiebreaker is needed.

SELECT
  LOWER(err_type) AS error_type,
  SUM(count)      AS occurrences
FROM err_tracks
GROUP BY LOWER(err_type)
ORDER BY occurrences DESC;

Row count on the raw spelling

GROUP BY err_type with COUNT(*): 10 rows, every one showing 20. 'TYPEERROR' and 'TypeError' sit on separate lines, and nothing in the list says which error fires most.

Tally on the folded spelling

GROUP BY LOWER(err_type) with SUM(count): 8 rows, 'typeerror' at 39,489 on top and 'nullpointerexception' at 34,252 second. The order finally means something.

Common follow-up questions

• How would you add each error type's share of all occurrences? (Tests a whole-result total alongside each group, without a second pass over `err_tracks`.)
• What if the review should cover only errors first seen in the last 30 days? (Tests filtering on `first_at` before the aggregate, and whether the partitioning then prunes the scan.)
• What if the tracker starts writing 'Type Error' with a space as well? (Tests whether the cleaning rule generalizes past case: stripping spaces, or a mapping table of known aliases.)