When It Rains

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

Lyft

Problem

The SRE team is hunting compound failures, the records where several independent alarms trip at once. Surface every error record where at least two of these markers appear: 'Error' in the type name, 'null' in the message, 'api' in the service name, or a severity of 'error' or 'ERROR'.

The worst failures never arrive alone.

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 multi-condition filter disguised as SRE triage. The real skill: turning four independent checks (two substring patterns, a service-name match, and a value set) into one scoring predicate, then keeping only the rows that trip at least two of them. Anyone can write four CASE expressions. The trick is summing them into a single integer and comparing that sum to 1, not chaining ORs that can tell you one signal fired but never that two did. Get the counting wrong and you either flag rows that match a single condition or silently drop rows that match exactly two.

Building the predicate

Step 1: Score each signal as 0 or 1

Each condition becomes a CASE WHEN ... THEN 1 ELSE 0 expression that yields 1 when it fires and 0 otherwise. err_type LIKE '%Error%' and message LIKE '%null%' are substring matches; svc_name LIKE '%api%' catches any service whose name embeds 'api'; severity uses an IN list against 'error' and 'ERROR' because it is an exact value, not a pattern.

Step 2: Sum the flags and threshold at 1

Add the four 0/1 expressions and keep rows where the total is greater than 1. Summing is what lets you count how many signals fired: a chain of ORs proves at least one is true but can never assert that two are. The > 1 threshold is the whole definition of 'compound' here.

The solution

SELECT err_id, err_type, message, svc_name, severity, count, first_at
FROM err_tracks
WHERE (CASE WHEN err_type LIKE '%Error%' THEN 1 ELSE 0 END
     + CASE WHEN message LIKE '%null%' THEN 1 ELSE 0 END
     + CASE WHEN svc_name LIKE '%api%' THEN 1 ELSE 0 END
     + CASE WHEN severity IN ('error', 'ERROR') THEN 1 ELSE 0 END) > 1

Common follow-up questions

• How would you change the query to flag only rows matching at least three of the conditions, or exactly two? (Tests whether the candidate can generalize the scoring threshold rather than treating > 1 as a magic constant.)
• SQLite's LIKE is case-insensitive for ASCII. How does that affect the '%Error%' and '%null%' checks, and why is severity matched with an IN list instead? (Tests awareness of LIKE case handling and why severity is treated as a value set.)
• `err_tracks.message` has roughly 5,000,000 distinct values. What would it take to avoid a full scan when the filter relies on a '%null%' substring match? (Tests indexing and scan-avoidance intuition on a 30M-row table with leading-wildcard predicates.)