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

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.

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

batch_jobs: 10 of 200 rows
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

First 5 of 6 rows
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 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

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

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