Against the Clock

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

L5

Asked in

Instacart

Problem

Our batch scheduler stores each job's start and end time as Unix epoch seconds, writing the end time only once a job finishes, so jobs still running or killed mid-flight have no end time. For the jobs that finished, report how many whole minutes each one ran, alongside its job_id and job_name.

Promised by noon. Delivered at midnight.

Tables

batch_jobs

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

Expected output

Worked solution and explanation

What this really tests

This is a duration calculation dressed up as job monitoring. The real skill: computing the gap between two timestamps while correctly dropping the records that have no end time yet. Anyone can subtract two numbers. The trap is the jobs still running or killed mid-flight: their ended value is NULL, and subtracting against NULL yields NULL, so if you compute on them you either pad the result with empty rows or silently invert the question, reporting phantom runtimes for work that never finished.

Why the finished-only scope is load-bearing

About 10 percent of these rows have no ended value at any moment, because a running job has not finished and a canceled one never will. Any arithmetic against a NULL returns NULL, so those rows would surface as blank durations. The business question is only about jobs that completed, so the scope has to be enforced before the arithmetic runs, not cleaned up afterward.

Step 1: Keep only the jobs that finished

Restrict to rows where ended is present. Comparing ended > started on top of that also guards against clock skew or reversed timestamps that would produce a negative runtime. Rows still running fall away here, which is exactly the intent.

Step 2: Turn the second gap into minutes

Both columns are Unix epoch seconds, so ended - started is the elapsed time in seconds. Integer-divide by 60 to get whole minutes, which truncates the leftover seconds so a run of 2 hours 11 minutes reports as 131.

The solution

SELECT job_id, job_name, (ended - started) / 60 AS minutes_elapsed
FROM batch_jobs
WHERE ended IS NOT NULL AND ended > started

Common follow-up questions

• If every job in the table were still running, what would this query return, and is that the behavior you want? (Tests whether the candidate reasons about the empty result rather than assuming rows.)
• How would you report the average runtime per `job_name`, and how do the unfinished jobs affect that average? (Tests extending the metric into an aggregate over the finished-only scope.)
• How would you return only the jobs that ran longer than 150 minutes, and where in the query does that predicate belong? (Tests turning the duration into a threshold filter, the natural next ask.)