# Job Status Duration > How long in each job state? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Tesla ## Problem In `batch_jobs`, each row records a recurring job, named by `job_name`, entering a new `status` at its `started` time, and the job stays in that status until its next row. For the platform team's capacity review, total the hours each job has spent 'running', counting a job's latest status as lasting 2 hours. ## 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 5 of 10 rows: | job_name | running_hours | | --- | --- | | backup_db | 1562 | | cache_warm | 1456 | | daily_report | 620 | | data_cleanup | 3016 | | email_blast | 1632 | ## Worked solution and explanation This is a per-job state timeline dressed up as a capacity review. Nothing in `batch_jobs` stores a duration: a span starts at a row's `started` and ends at the same job's next row, whatever status that row carries. Anyone can reach for `LEAD`. What separates candidates is **where the 'running' filter goes**: filter first and every running span stretches to the job's next running row, swallowing all the queued, failed and completed time in between. Then there is the open end. A job's latest row has no next change, and unless you hand it the 2 hours it quietly falls out of the sum. ### Read the whole timeline, then keep the running part #### Step 1: Find the key that is really the job `job_id` is unique on every row, so partitioning by it gives each row a timeline of one and `LEAD` returns `NULL` everywhere. The recurring job is `job_name`: twenty rows each, spread over several years. Spotting that from the sample rows is half the problem. #### Step 2: Pair each row with the job's next change `LEAD(started) OVER (PARTITION BY job_name ORDER BY started)` puts the next change's timestamp beside the current one as `next_started`. It has to run over every status: a running stretch ends when the job moves to anything at all, and that anything is usually not 'running'. #### Step 3: Decide each row's contribution in one `CASE` Non-running rows add 0. A running row with a next change adds `(JULIANDAY(next_started) - JULIANDAY(started)) * 24`, the gap in hours. A running row with no next change is the job's latest status and gets the 2.0 default. Branch order matters: the status test comes first so a non-running latest row never picks up the default. #### Step 4: Sum per job `SUM` per `job_name`. Because the filter lives inside the `CASE` rather than in a `WHERE` ahead of the window, every row took part in the pairing and the sum keeps only the running spans. **Running hours per job from its status timeline** ```sql WITH transitions AS ( SELECT job_name, status, started, LEAD(started) OVER (PARTITION BY job_name ORDER BY started) AS next_started FROM batch_jobs ) SELECT job_name, ROUND(SUM( CASE WHEN status <> 'running' THEN 0 WHEN next_started IS NOT NULL THEN (JULIANDAY(next_started) - JULIANDAY(started)) * 24 ELSE 2.0 END ), 2) AS running_hours FROM transitions GROUP BY job_name ``` *Pair over all statuses in `transitions`; filter inside the `SUM`.* **Filter, then pair** A `WHERE status = 'running'` before `LEAD` leaves only running rows in each partition, so `next_started` is the job's next RUNNING row. A run that failed and sat queued for a month is billed as a month of running. **Pair, then filter** `LEAD` sees every row, so `next_started` is the job's next change of any kind. The `CASE` then discards non-running rows, and each running span ends exactly when the job left 'running'. > **A `NULL` end is not zero hours** > > `SUM` skips `NULL`, so a running row with no next change vanishes without an error. On this data `user_export` ends in 'running', and a query that forgets the default comes back exactly 2 hours short for that job and looks perfectly plausible. > **The filter's position is the whole question** > > Say out loud why the status test is inside the `SUM` and not in a `WHERE` before the window. That one sentence shows you think about which rows a window sees, which is the thing this question is built to probe. > **One sort per job, one pass** > > The window sorts `batch_jobs` by `job_name, started` once, and the grouped sum reads that same grain. A correlated subquery hunting `MIN(started)` after each row does the same pairing but probes the table once per row: at 600K rows that is a sort against a nested scan. ## Common follow-up questions - How would you report the hours in every status per job, with 'Completed' and 'completed' treated as one status? _(Tests generalizing the `CASE` into a grouped breakdown with normalized keys.)_ - What if two rows of the same `job_name` shared a `started` timestamp? _(Tests seeing that `LEAD` becomes nondeterministic without a tiebreaker in the window order.)_ - Instead of a flat 2 hours, how would you close a latest running span at a reporting cutoff time? _(Tests replacing the default with a parameterized end bound.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/job_status_duration) - [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.