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

The on-call team's job report keeps breaking on batch_jobs rows that violate its two rules: every job should have an ended time by now, and no job should end before it started. List every job that breaks either rule with its job_id, job_name and runtime in whole minutes from started to ended.

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 constraint audit dressed up as a broken runtime report. The job table promises two things: every job carries an ended time, and that time never precedes started. The skill is finding the rows that break either promise and reporting their runtime. The trap is believing one test covers both. Filter on minutes_elapsed < 0 and you catch the jobs that ended before they started, but a job with no ended has a runtime of NULL, NULL is never less than zero, and all 50 stuck jobs silently drop out of the audit.

Two rules, two tests

Step 1: Test the missing end directly

ended IS NULL is the only way to ask whether a value is absent. ended = NULL is never true, and ended = '' compares against empty text the table never stores. Do not excuse 'running' or 'queued' rows by status: the report treats every job as overdue for an end time, and status does not line up with ended here anyway (some 'running' rows carry one).

Step 2: Compare the times as they are stored

started and ended are fixed-width 'yyyy-MM-dd HH:mm:ss' strings, so text order is time order and ended < started flags the end-before-start rows without converting anything. Join the two tests with OR: a row only has to break one rule to be reported.

Step 3: Convert before you subtract

Ordering survives as text; arithmetic does not. A lenient engine reads each string as its leading number, the year, so ended - started comes out 0. Cast both to TIMESTAMP, subtract to get an interval, take EXTRACT(EPOCH FROM ...) for seconds and divide by 60. A job stamped as starting at 22:00 and ending at 00:02 on the same date reports -1318.

The solution

SELECT job_id,
       job_name,
       EXTRACT(EPOCH FROM CAST(ended AS TIMESTAMP) - CAST(started AS TIMESTAMP)) / 60 AS minutes_elapsed
FROM batch_jobs
WHERE ended IS NULL
   OR ended < started

SELECT job_id,
       job_name,
       EXTRACT(EPOCH FROM CAST(ended AS TIMESTAMP) - CAST(started AS TIMESTAMP)) / 60 AS minutes_elapsed
FROM batch_jobs
WHERE EXTRACT(EPOCH FROM CAST(ended AS TIMESTAMP) - CAST(started AS TIMESTAMP)) < 0

Common follow-up questions

• How would you add a column saying which rule each job broke? (Tests labeling each violation rather than only listing offenders.)
• If running jobs were allowed to have no end, how would the missing-end rule change given how `status` is spelled? (Tests scoping a rule by a column with mixed casing.)
• Every end-before-start job started at 22:00 or later. What would you suspect about how `ended` is recorded, and how would you repair those runtimes? (Tests reasoning about the cause of a constraint violation.)