Clean Exit
An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- easy
- Seniority
- L3
- Asked in
- Meta
Problem
Return the job IDs of every priority-1 job in batch_jobs that finished successfully. Different schedulers log the same status with different capitalization, and a completed job counts as cleared however its status was written.
Priority one. Only some of them made it to the end.
Tables
batch_jobs
Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER
| job_id | job_name | status | rows_done | started | ended | priority | retries |
|---|---|---|---|---|---|---|---|
| 1047 | user_export | running | 97 | 2026-02-02 01:00:00 | 2026-02-02 03:11:00 | 3 | 0 |
| 1094 | data_cleanup | failed | 194 | 2026-03-03 02:00:00 | 2026-03-03 04:22:00 | 6 | 0 |
| 1141 | email_blast | queued | 291 | 2026-04-04 03:00:00 | 2026-04-04 05:33:00 | 9 | 0 |
| 1188 | index_rebuild | canceled | 388 | 2026-05-05 04:00:00 | NULL | 2 | 0 |
| 1235 | cache_warm | Completed | 485 | 2026-06-06 05:00:00 | 2026-06-06 07:55:00 | 5 | 0 |
| 1282 | log_archive | FAILED | NULL | 2026-07-07 06:00:00 | 2026-07-07 08:06:00 | 8 | 0 |
| 1329 | metric_agg | completed | 679 | 2026-08-08 07:00:00 | 2026-08-08 09:17:00 | 1 | 0 |
| 1376 | backup_db | running | 776 | 2026-09-09 08:00:00 | NULL | 4 | 0 |
| 1423 | sync_elastic | failed | 873 | 2026-01-10 09:00:00 | 2026-01-10 11:39:00 | 7 | 5 |
| 1470 | daily_report | queued | 970 | 2026-02-11 10:00:00 | 2026-02-11 12:50:00 | 0 | 0 |
Expected output
| job_id |
|---|
| 1329 |
| 3209 |
| 4619 |
| 10005707 |
| 10005747 |
Worked solution and explanation
What this really tests
This is a dirty-data filter wearing a status-lookup costume. The status column records the same job state in more than one casing: some rows say 'completed', others say 'Completed'. Anyone can type WHERE status = 'completed'. What the interviewer is watching for is whether you noticed the casing and normalized it before comparing. Miss it and you silently drop every priority-1 job that happened to be logged with a capital C: on this data that is two of the six, and the on-call check 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 there are no casing concerns; 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
SELECT job_id
FROM batch_jobs
WHERE LOWER(status) = 'completed'
AND priority = 1Common 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 with `job_name` and `ended` 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.)