Job Status Duration

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

How long in each job state?

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

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.

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

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

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