# Against the Clock > Promised by noon. Delivered at midnight. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L5 ## 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. ## 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 **Finished jobs with their runtime in minutes** ```sql SELECT job_id, job_name, (ended - started) / 60 AS minutes_elapsed FROM batch_jobs WHERE ended IS NOT NULL AND ended > started ``` > **Trick to solving** > > Because both times are plain integers of seconds, the whole thing is one subtraction and a divide by 60. No date-part functions, no timezone handling: the epoch encoding turns an interval question into arithmetic. > **Common pitfall** > > The reflex is to subtract the two columns across all rows and move on. That quietly includes every job with a missing `ended`, where the subtraction returns NULL, so your result set is padded with rows whose runtime is empty. On this table that is roughly 1 in 10 rows carrying a wrong answer. > **Performance insight** > > With about 300K rows this is a single sequential scan and a per-row arithmetic expression, so it stays cheap. The `ended IS NOT NULL` predicate is not selective enough to justify an index here; the scan dominates and that is fine at this size. > **Interviewers watch for** > > The tell of a senior candidate is naming the missing-end-time rows before writing any SQL and deciding what they mean, rather than discovering them when the numbers look off. Noticing the reversed-timestamp row that gets excluded is a second signal. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/against_the_clock) - [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.