Ghosts in the Scheduler

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

Southwest Airlines

Problem

Status values in batch_jobs were typed by hand, so a finished job can read 'completed' in more than one casing, and some of those completed jobs never wrote an ended timestamp. For the platform team's audit of jobs started in 2024, count the jobs marked completed, in any casing, that still have no end time recorded.

It says running. It has been running.

Tables

batch_jobs

Columns: job_id INTEGER, job_name TEXT, status TEXT, rows_done INTEGER, started TEXT, ended TEXT, priority INTEGER, retries INTEGER

Expected output

Worked solution and explanation

What this really is

Strip the costume and this is a count behind three filters, and one of them is a lie detector. The count is trivial. What separates candidates is noticing that status was typed by hand, so the same outcome arrives as 'completed' and 'Completed'. A plain status = 'completed' runs clean and returns 1 for 2024 when the real answer is 2: half the ghosts vanish, and nobody is warned. The second filter is the absent end time, which only ended IS NULL sees; ended = NULL matches nothing at all.

Build it up

Step 1: Fence the calendar year on started

started is text in 'YYYY-MM-DD HH:MM:SS' form, so string comparison sorts chronologically. Use started >= '2024-01-01' AND started < '2025-01-01': the half-open bound keeps a job started at 23:00 on December 31 and leaves January 1 of the next year out.

Step 2: Fold the casing before comparing

LOWER(status) = 'completed' maps 'Completed' onto 'completed', so both spellings of the same outcome count. String equality is case-sensitive here, so skipping this silently drops a real row.

Step 3: Test for absence, not equality

ended IS NULL keeps the jobs that never wrote an end time. NULL is not equal to anything, itself included, so ended = NULL evaluates to unknown on every row and keeps none.

Step 4: Count the survivors

COUNT(*) collapses what is left into a single number, named completed_without_end_count so the result says what it measures.

SELECT COUNT(*) AS completed_without_end_count
FROM batch_jobs
WHERE started >= '2024-01-01'
  AND started < '2025-01-01'
  AND ended IS NULL
  AND LOWER(status) = 'completed'

Trusts the data

status = 'completed' returns 1, and swapping in ended = NULL drops it to 0. Both run without an error.

Distrusts the data

LOWER(status) = 'completed' with ended IS NULL returns 2, the real number of jobs that claim success but never finished.

Common follow-up questions

• What changes if some jobs store an empty string in `ended` instead of a NULL? (Tests whether the candidate folds blank strings into the absence check.)
• Your `LOWER(status)` call may prevent an index from being used. How would you keep the case-insensitive match index-friendly? (Probes expression indexes or normalizing `status` at write time.)
• How would you report these ghost jobs per `job_name` instead of as one total? (Checks whether the same filters carry over to a grouped count.)