# Ghosts in the Scheduler > It says running. It has been running. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem Our batch scheduler keeps a fleet of jobs that still claim to be running but never recorded an end time. Among jobs that started during 2025, count how many report a running status, in whatever casing, and still have no `ended` timestamp. ## Worked solution and explanation ### Why this problem exists in real interviews Strip the costume and this is a two-predicate filter with one trap. The count itself is trivial; what actually separates candidates is noticing that `status` is dirty. The data carries 'running', 'Running', and 'RUNNING', so a plain `status = 'running'` quietly returns a third of the real answer and nobody flags it because the query runs clean. The interviewer is watching whether you distrust the data enough to normalize case, and whether you reach for `ended IS NULL` instead of the `ended = NULL` that silently matches nothing. --- ### Break down the requirements #### Step 1: Filter to jobs started in the target year `WHERE started >= '2025-01-01' AND started < '2026-01-01'` restricts to jobs that started anytime during the prior calendar year. The half-open range keeps late-December rows without accidentally pulling in January of the next year. #### Step 2: Normalize the status, then filter `AND LOWER(status) = 'running'` folds every casing of the status onto one value before comparing, so 'Running' and 'RUNNING' are counted alongside 'running'. Skip this and you undercount badly on real, human-entered status fields. #### Step 3: Filter to no end timestamp `AND ended IS NULL` ensures the job never recorded an end timestamp. NULL is not equal to anything, itself included, so `ended = NULL` would return zero rows. #### Step 4: Count the result `SELECT COUNT(*)` collapses the surviving rows into the single stuck-running count. --- ### The solution **Count jobs stuck in running with no end time** ```sql SELECT COUNT(*) AS stuck_running_count FROM batch_jobs WHERE started >= '2025-01-01' AND started < '2026-01-01' AND ended IS NULL AND LOWER(status) = 'running' ``` > **Cost Analysis** > > The query scans `batch_jobs` (400,000 rows). A covering index on `started` plus the filter columns would reduce I/O. At this scale the full scan is acceptable, but it becomes costly if the table grows 10x. > **Interviewers Watch For** > > The tell is whether you touch the status casing at all. A candidate who writes `status = 'running'` and moves on has trusted the data; a strong one eyeballs the distinct statuses, sees the mixed case, and normalizes. They also use `IS NULL`, not `= NULL`, and sanity-check the count against the visible rows before declaring victory. > **Common Pitfall** > > The most common mistake is comparing `status = 'running'` without normalizing case, which here silently drops the 'Running' and 'RUNNING' jobs and returns 1 instead of 3. The second is writing `ended = NULL` instead of `ended IS NULL`, which matches nothing. Both run without error, so nothing warns you. --- ## Common follow-up questions - Why does `ended = NULL` return no rows, and how does `ended IS NULL` correctly capture jobs that never recorded an end time? _(Probes understanding of NULL semantics and why `IS NULL` is required instead of an equality comparison on `batch_jobs.ended`.)_ - Your `LOWER(status)` call may prevent an index from being used. How would you restructure the filter or schema to keep the case-insensitive match index-friendly? _(Tests understanding of how wrapping a column in a function like LOWER() can prevent index usage on `batch_jobs.status`.)_ - If `batch_jobs` grew to billions of rows, which part of your query would become the bottleneck given the cardinality of `started`? _(Tests ability to identify performance hotspots related to `batch_jobs.started` at scale.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/ghosts_in_the_scheduler) - [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.