Non-Trivial Fatal Errors

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

Capital One

Problem

We bucket error messages by character length: 'short' is under 30 characters, 'mid' is 30 to 31, and 'long' is 32 or more. Return the fatal errors that are not short, where an error is fatal only when its severity reads exactly 'Fatal' or 'fatal', showing each one's error ID, message, service name, and length bucket, one row per error.

Short errors are noise. Long ones matter.

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

Strip the error-triage story away and this is a precise-boundary problem wearing an ops costume. The real skill is turning fuzzy English ('short/mid/long', 'fatal') into exact predicates, and two boundaries decide whether you pass. First, severity is written in two casings, 'Fatal' and 'fatal', so an equality check on one spelling silently loses every row spelled the other way. Second, 'short' is a floor you enforce in the filter (drop anything under 30 characters), while 'mid' versus 'long' is a split you make inside a CASE. Blur either one and you keep terse noise you were told to drop, or you mislabel every message sitting on the boundary.

Why a simpler query is wrong here

It is tempting to skip the length floor and just categorize everything, or to skip the CASE and return a flat list. Both fail on this data. Without LENGTH(message) >= 30 you pull back 66 rows instead of 53, because 13 fatal messages are exactly 28 characters and belong in 'short'. Without the CASE you cannot separate the 27 'mid' rows from the 26 'long' rows at all. Every piece of this query changes the answer.

Build it step by step

Step 1: Match fatal, both casings

Match fatal in both spellings with WHERE severity IN ('Fatal', 'fatal'). A single equality like severity = 'Fatal' looks right and quietly drops every lowercase row. LOWER(severity) = 'fatal' also works on this data, but the point is the same: cover both casings deliberately.

Step 2: Drop the short messages

Enforce the 'short' cutoff in the filter with AND LENGTH(message) >= 30. This is where many answers leak: they compute the category correctly but never actually exclude the short messages, so terse errors survive into the output.

Step 3: Split mid from long

Label the survivors with CASE WHEN LENGTH(message) <= 31 THEN 'mid' ELSE 'long' END. The <= 31 keeps the boundary inclusive on the mid side, so a 31-character message is 'mid' and a 32-character message is 'long'.

Step 4: Project the four columns

Return err_id, message, svc_name, and the computed bucket. The count column has nulls and is a distractor: it plays no part in the answer.

The solution

SELECT
    err_id,
    message,
    svc_name,
    CASE
        WHEN LENGTH(message) <= 31 THEN 'mid'
        ELSE 'long'
    END AS length_category
FROM err_tracks
WHERE severity IN ('Fatal', 'fatal')
  AND LENGTH(message) >= 30

Common follow-up questions

• Suppose we add a fourth bucket for very long messages over 60 characters. How does your CASE change, and how do you keep the boundaries from overlapping? (Tests whether they generalize the two-way split into an N-way bucketing with clean, non-overlapping boundaries.)
• This scans 25M rows. What index would make the LENGTH(message) filter fast, and why does a normal index on message not help? (Tests indexing knowledge for non-sargable expression predicates on a large table.)
• If a new service starts writing severity as 'FATAL' in all caps, would your query catch it, and how would you make the match resilient to new casings? (Tests robustness of the severity match against future data drift.)