Error Severity Buckets

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

Airbnb

Problem

On-call wants every error it has actually measured tagged with a severity label based on how often that error has fired: 0 occurrences is NONE, 1 to 5 is LOW, 6 to 20 is MODERATE, 21 to 50 is HIGH, and anything above 50 is CRITICAL. An error whose fire count was never recorded can't be sized, so leave it out, and show each error type alongside its label.

Errors sorted by how much they hurt.

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 tests

This is a five-way numeric bucketing over an occurrence count, with a NULL filter doing the quiet work. Anyone can write a CASE over a count column; what actually separates candidates is realizing a missing count is not a number. Skip the filter and every unrecorded count slides past count = 0 and all four BETWEEN tests straight into the ELSE arm, coming back CRITICAL. Your critical list then fills with errors nobody ever actually sized, burying the ones that genuinely fired dozens of times. The fix is one predicate: WHERE count IS NOT NULL keeps the unmeasured errors out of the report entirely.

Walk the requirements

Step 1: Drop the unsized errors

Start with WHERE count IS NOT NULL. An error whose occurrence count was never written is one nobody has sized, and on-call can't rank a severity it can't measure, so it leaves the report. Everything that survives produces exactly one output row.

Step 2: Define the five buckets

Map count to a label with an ordered CASE: 0 is NONE, 1 to 5 is LOW, 6 to 20 is MODERATE, 21 to 50 is HIGH, and the ELSE arm catches everything above 50 as CRITICAL. BETWEEN is inclusive on both ends, so 5 stays LOW and 6 becomes MODERATE.

Step 3: Check 0 before the ranges

Order matters: the first matching branch wins, so test count = 0 before the ranges. A recorded zero is a real measurement (the error exists but has not fired) and maps to NONE, which is a different case from the never-recorded counts you already filtered out.

Step 4: Project two columns

Return only err_type and the labeled column. Don't carry through count, err_id, or svc_name.

The solution

SELECT
    err_type,
    CASE
        WHEN count = 0 THEN 'NONE'
        WHEN count BETWEEN 1 AND 5 THEN 'LOW'
        WHEN count BETWEEN 6 AND 20 THEN 'MODERATE'
        WHEN count BETWEEN 21 AND 50 THEN 'HIGH'
        ELSE 'CRITICAL'
    END AS severity_label
FROM err_tracks
WHERE count IS NOT NULL

Wrong: no filter, NULL slides to CRITICAL

A CASE with no WHERE guard. A missing count matches neither count = 0 nor any BETWEEN range, so it falls to ELSE = 'CRITICAL'. The one error nobody sized now tops the severity list, crowding out the errors that genuinely fired dozens of times.

Right: filter the unsized errors out

WHERE count IS NOT NULL removes the unmeasured rows before the CASE ever runs. count = 0 still maps to NONE, and only genuine counts above 50 reach CRITICAL.

Common follow-up questions

• How would you count how many errors fall in each severity bucket? (Tests wrapping the CASE in a subquery or CTE and grouping on the label.)
• How would you make the bucket thresholds configurable without editing the query each time? (Tests replacing hardcoded thresholds with a range join against a config table.)
• If the rule changed so an unrecorded count should be labeled CRITICAL instead of dropped, what would you change? (Tests reasoning about NULL semantics: removing the filter and adding an explicit NULL branch or leaning on ELSE.)