Average Machine Job Time

Every machine on the factory floor logs a 'start' event when it begins a job and a 'finish' event when the job is done. Job numbers restart on each machine, so job 1 on one press is unrelated to job 1 on another.

For each machine, compute the average job duration in seconds (finish time minus start time), counting only jobs that have both a start and a finish. A job that is still running has only a start event and must be ignored.

Return machine_id and avg_seconds, rounded to 2 decimal places, in any order. Machines with no finished job do not appear.

Tables

machine_events — One row per start or finish event of a job on a machine.

Example

machine_events

Expected output

Rows may be returned in any order. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Joins · problem 18 of 70 · SQLite

The idea

A job's duration needs two rows: its start and its finish. Joining the table to itself turns those two rows into one. The left copy s keeps only 'start' events. The right copy f must be the 'finish' event for the same machine and the same job number. Matching on the job number alone is not enough, because job numbers restart on every machine.

Each joined row is one complete job, so f.at_second - s.at_second is its duration. A job that is still running has no finish row and disappears from the inner join, so it cannot distort the average. Grouping by machine and applying AVG gives the answer. AVG always returns a real number, so ROUND(..., 2) keeps the fraction.

The alternative pivots each job into one row with conditional aggregation (MAX(CASE ...)) and then drops jobs that are missing either time.

Solution

SELECT s.machine_id,
       ROUND(AVG(f.at_second - s.at_second), 2) AS avg_seconds
FROM machine_events AS s
JOIN machine_events AS f
  ON f.machine_id = s.machine_id
 AND f.job_no = s.job_no
 AND f.event_type = 'finish'
WHERE s.event_type = 'start'
GROUP BY s.machine_id;

Another way

WITH jobs AS (
  SELECT machine_id,
         job_no,
         MAX(CASE WHEN event_type = 'start' THEN at_second END) AS started,
         MAX(CASE WHEN event_type = 'finish' THEN at_second END) AS finished
  FROM machine_events
  GROUP BY machine_id, job_no
)
SELECT machine_id,
       ROUND(AVG(finished - started), 2) AS avg_seconds
FROM jobs
WHERE started IS NOT NULL
  AND finished IS NOT NULL
GROUP BY machine_id;

Why common answers fail

Joins on the job number alone, so job 1 on one machine is paired with job 1 on every other machine.

SELECT s.machine_id, ROUND(AVG(f.at_second - s.at_second), 2) AS avg_seconds
FROM machine_events AS s
JOIN machine_events AS f
  ON f.job_no = s.job_no
 AND f.event_type = 'finish'
WHERE s.event_type = 'start'
GROUP BY s.machine_id;

Subtracts the average start from the average finish, which is only right when every job has finished; a running job shifts the start average.

SELECT machine_id,
       ROUND(AVG(CASE WHEN event_type = 'finish' THEN at_second END)
           - AVG(CASE WHEN event_type = 'start' THEN at_second END), 2) AS avg_seconds
FROM machine_events
GROUP BY machine_id;

Divides two integers, so SQLite truncates the average before rounding (an average of 1.67 becomes 1).

SELECT s.machine_id, ROUND(SUM(f.at_second - s.at_second) / COUNT(*), 2) AS avg_seconds
FROM machine_events AS s
JOIN machine_events AS f
  ON f.machine_id = s.machine_id
 AND f.job_no = s.job_no
 AND f.event_type = 'finish'
WHERE s.event_type = 'start'
GROUP BY s.machine_id;

What the interviewer is checking

Checks that you can pair related event rows with a self-join on the full key, and that you think about incomplete events before averaging.

Mistakes to avoid

• Joining on job number only, when job numbers repeat across machines.
• Averaging starts and finishes separately, which breaks as soon as one job is unfinished.
• SUM / COUNT on integers, which truncates in SQLite.

Follow-ups to expect

• Also return the longest job per machine (MAX of the same difference).
• Include machines with no finished job showing NULL (start from a list of machines and LEFT JOIN).
• What if a job could be started twice? (Pick the latest start before the finish, e.g. with a correlated subquery.)

In real work

Cycle-time and turnaround reporting from event logs: manufacturing jobs, support tickets, CI builds, API request traces.

Practice next