# When It Rains

> The worst failures never arrive alone.

Canonical URL: <https://datadriven.io/problems/when_it_rains>

Domain: SQL · Difficulty: medium · Seniority: L4

## 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'.

## 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

**Sum four 0/1 flags and keep totals above one**

```sql
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
```

> **Cost Analysis**
>
> With ~30M rows and leading-wildcard LIKE predicates, every branch forces a sequential scan: no B-tree index can seek into a '%null%' match. At this scale you would push this filter to a scan-friendly columnar store or precompute the four flags at ingest so the read is a cheap sum.

> **Interviewers Watch For**
>
> Whether you reach for arithmetic over booleans. Candidates who OR the conditions together produce a filter that cannot express 'at least two'; candidates who sum 0/1 flags show they understood the counting requirement. Interviewers also watch whether you treat severity as an exact value set instead of forcing it into a LIKE.

> **Common Pitfall**
>
> Dropping or misplacing the % wildcard changes the match entirely. `LIKE 'Error%'` matches only strings that start with 'Error' and would miss 'TypeError'; the substring form `LIKE '%Error%'` is what catches it anywhere in the string.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/when_it_rains)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.