Error Category Breakdown

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

L4

Asked in

City and County of San Francisco

Problem

Each row of err_tracks is one tracked error: its err_type was never normalized for letter case, and its count column holds how many times that error fired. Put every track into a 'timeout' category when its error type mentions a timeout, 'memory' when it mentions memory, and 'other' otherwise, and report how many tracks (not occurrences) land in each category.

Postmortem time. Categorize the errors.

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

This is a label you have to invent, dressed up as an error report. No column holds 'timeout' or 'memory'. You build that label row by row from err_type, then count rows under it. Writing a CASE is the easy part. The two traps are the ones the statement hints at. First, err_type is spelled in mixed case ('ConnectionTimeout', 'OutOfMemory', 'TYPEERROR'), so a case-sensitive pattern like '%timeout%' matches nothing and every row collapses into 'other'. Second, the table has a column named count, and SUM(count) gives occurrence totals instead of track counts.

Step 1: Normalize before you match

Wrap the column as LOWER(err_type) before any LIKE. 'ConnectionTimeout' only contains 'timeout' after lowercasing. Without that, the result is a single 'other' row holding all 200 tracks. It is a valid query that answers nothing.

Step 2: Derive the label with CASE

Test timeout first, then memory, and send everything else to ELSE 'other'. Branch order matters: CASE returns the first branch that matches. An error type that mentioned both a timeout and memory would count as a timeout.

Step 3: Group on the label, count rows

Group by the derived error_category, not by err_type. Grouping by the raw column gives one row per spelling. Count with COUNT(*), because each row is one track. count is an occurrence tally, and it is also NULL on some rows.

SELECT
    CASE
        WHEN LOWER(err_type) LIKE '%timeout%' THEN 'timeout'
        WHEN LOWER(err_type) LIKE '%memory%' THEN 'memory'
        ELSE 'other'
    END AS error_category,
    COUNT(*) AS track_count
FROM err_tracks
GROUP BY error_category

Looks right

err_type LIKE '%timeout%' with SUM(count). On an engine with case-sensitive LIKE, no row matches. Even when rows do match, the numbers are occurrences, and rows with a NULL count drop out silently.

Is right

LOWER(err_type) LIKE '%timeout%' with COUNT(*). Matching ignores casing, and every track counts exactly once whatever its occurrence tally.

Common follow-up questions

• How would you also report total occurrences per category? (Tests whether `SUM(count)` belongs next to `COUNT(*)`, and how a NULL `count` should be treated.)
• What if a new error type like 'MemoryTimeout' appears? (Tests understanding of `CASE` branch order and explicit precedence.)
• How would you keep the bucket rules out of the query? (Tests moving the patterns into a mapping table joined on a match condition.)