# Ghosts in the Scheduler > It says running. It has been running. Canonical URL: 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. ## Tables ### batch_jobs - job_id INTEGER - job_name TEXT - status TEXT - rows_done INTEGER - started TEXT - ended TEXT - priority INTEGER - retries INTEGER Sample rows (10 of 200): | 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 1 of 1 rows: | completed_without_end_count | | --- | | 2 | ## 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. **Count completed jobs with no end time** ```sql 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. > **`BETWEEN` on a text timestamp loses New Year's Eve** > > `started BETWEEN '2024-01-01' AND '2024-12-31'` looks complete. But '2024-12-31 08:00:00' sorts after '2024-12-31', so every job started during that last day falls out. The half-open range has no such hole. > **Look at the status values before you filter on them** > > The tell is whether you check the status values at all. A candidate who writes `status = 'completed'` and moves on has trusted the data. A strong one lists the statuses, sees 'Completed' next to 'completed', normalizes, and sanity-checks the count against the visible rows. > **A function on the column hides it from a plain index** > > On 400,000 rows a full scan is fine. At larger scale, an index on `started` still serves the range, but `LOWER(status)` cannot use a plain index on `status`. An expression index on `LOWER(status)`, or normalizing at write time, keeps the match cheap. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/pending_batch_jobs) - [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.