# Clean Exit

> Priority one. Only some of them made it to the end.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The on-call team is checking which of the critical jobs cleared during the overnight run. Return the job IDs of every priority-1 job that finished successfully.

## Worked solution and explanation

### What this really tests

This is a dirty-data filter wearing a status-lookup costume. The status column records the exact same job state in more than one casing: some rows say 'completed', others say 'Completed'. Anyone can type WHERE status = 'completed'. The tell the interviewer is watching for is whether you NOTICED that and normalized the casing before comparing. Miss it and you silently drop every priority-1 job that happened to be logged with a capital C, and your on-call report quietly under-counts the very jobs it exists to verify.

---

### The trap: inconsistent casing

Look at the sample rows before writing anything. 'completed' and 'Completed' both appear, and 'failed' shows up as both 'failed' and 'FAILED'. That is production reality: different services write the same state with different casing. A case-sensitive equality test treats them as different values, so the result depends on which service happened to log the row. That is a correctness bug, not a style choice.

**Case-sensitive (wrong here)**

WHERE status = 'completed' matches only the lowercase rows and drops every job written as 'Completed'. The count silently changes with the data's casing.

**Normalized (correct)**

WHERE LOWER(status) = 'completed' folds every casing to one form, so a completed job counts as completed no matter which service logged it.

---

### Building the filter

#### Step 1: Normalize the status, then match

Fold status to a single casing with LOWER() and compare to 'completed'. This is the one decision that separates a correct answer from a plausible-looking wrong one on this data.

#### Step 2: Restrict to priority 1

Add the priority = 1 predicate. Priority is an integer, so no casing concerns there; a plain equality is exact.

#### Step 3: Return only the IDs

SELECT job_id returns just the identifiers the on-call check needs, nothing more.

---

### The solution

**Case-normalized filter**

```sql
SELECT job_id
FROM batch_jobs
WHERE LOWER(status) = 'completed'
  AND priority = 1
```

> **Interviewers watch for**
>
> The moment you saw two casings of the same status word, LOWER() (or a case-insensitive collation) should have been reflexive. Candidates who write status = 'completed' against mixed-case data are the ones who get burned in production; interviewers plant the mixed casing on purpose to see who checks.

> **The cost of normalizing**
>
> LOWER(status) wraps the column in a function, so a plain B-tree index on status can no longer be used for this predicate: the planner falls back to a full scan of 200K rows. If this ran hot, you would add a functional index on LOWER(status) or store status under a case-insensitive collation so the normalized comparison stays index-backed.

---

## Common follow-up questions

- The team now wants the job name and end time alongside each ID. What changes? _(Tests whether they extend the SELECT list without touching the filter logic.)_
- How would you list the priority-1 jobs that did NOT finish successfully? _(Tests negation plus the same casing awareness (LOWER(status) <> 'completed').)_
- This query scans the whole table because of LOWER(). How would you make it index-backed? _(Tests knowing the real fix: a functional index on LOWER(status) or a case-insensitive collation.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/clean_exit)
- [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.