# Rows With Multiple Flag Conditions

> Rows caught by multiple flags.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The SRE team is triaging compound error conditions where multiple signals fire at once. An error record is flagged if it matches more than one of these: err_type contains 'Error', message contains 'null', svc_name contains 'api', severity is 'error' or 'ERROR'. Return all flagged rows with their error ID, type, message, service, severity, occurrence count, and first occurrence time.

## Worked solution and explanation

### Why this problem exists in real interviews

This challenge asks you to apply pattern matching with LIKE to the `err_tracks` table, simulating a real reliability engineering workflow. Pay attention to columns like `err_id`, `err_type`, and `message` as they drive the aggregation and output.

---

### Break down the requirements

#### Step 1: Classify rows with a CASE expression

The `CASE` expression evaluates conditions top to bottom and returns the first match. Order matters: put the most restrictive condition first to avoid misclassification.

#### Step 2: Select the target columns

The SELECT clause picks exactly the columns the prompt asks for. Returning extra columns or missing a required alias would fail the grading check.

---

### The solution

**Pattern-match for rows with multiple flag conditions**

```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, the query performs a single sequential scan. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether the query returns exactly the columns and ordering the prompt specifies; how quickly you identify the core operation and write clean, minimal code.

> **Common Pitfall**
>
> Forgetting the `%` wildcard or placing it on the wrong side changes the match semantics entirely. `LIKE 'x%'` matches prefixes; `LIKE '%x'` matches suffixes.

---

## Common follow-up questions

- What would happen to your result if `err_tracks.first_at` contained duplicate values that you did not expect? _(Tests whether the candidate considers data quality issues in `first_at` and uses DISTINCT or deduplication where needed.)_
- `err_tracks.message` has roughly 5,000,000 distinct values. What index strategy would you use to avoid a full scan on `err_tracks`? _(Tests indexing knowledge specific to the high-cardinality `message` column in `err_tracks`.)_
- Your conditional CASE logic assumes the categories are exhaustive. What happens if a row in `err_tracks` falls into none of the branches? _(Tests awareness of the implicit ELSE NULL in CASE expressions.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/rows_with_multiple_flag_conditions)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.