Average Process Time Per Machine SQL Query

Problem statement

Prompt

Given the activity table below (each row marks a start or end timestamp for a (machine, process) pair), write a query that returns each machine's average time to complete a process.

The processing time of a (machine, process) pair is end_timestamp - start_timestamp.
Output columns: machine_id and processing_time, rounded to 3 decimal places with ROUND(..., 3).
Order the result by machine_id.

Example

machine_id	process_id	activity_type	timestamp
0	0	start	0.712
0	0	end	1.520
0	1	start	3.140
0	1	end	4.120
1	0	start	0.500
1	0	end	2.000

Expected result:

machine_id	processing_time
0	0.894
1	1.5

Constraints

For every start row there is exactly one matching end row for the same (machine, process).
timestamp is a floating point value in seconds.

Tips

Self-join activity to itself on (machine_id, process_id) matching a start to its end, then AVG(end.timestamp - start.timestamp).
A CASE WHEN pivot (one row per process with start and end columns) also works.

Schema

CREATE TABLE activity (
    machine_id    INTEGER NOT NULL,
    process_id    INTEGER NOT NULL,
    activity_type TEXT NOT NULL CHECK (activity_type IN ('start', 'end')),
    timestamp     REAL NOT NULL,
    PRIMARY KEY (machine_id, process_id, activity_type)
);

Sample data

INSERT INTO activity (machine_id, process_id, activity_type, timestamp) VALUES
  (0, 0, 'start', 0.712),
  (0, 0, 'end',   1.520),
  (0, 1, 'start', 3.140),
  (0, 1, 'end',   4.120),
  (1, 0, 'start', 0.500),
  (1, 0, 'end',   2.000),
  (1, 1, 'start', 2.500),
  (1, 1, 'end',   3.500);

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: average process duration per machine (start/end self join).
SELECT s.machine_id,
       ROUND(AVG(e.timestamp - s.timestamp), 3) AS processing_time
FROM activity s
JOIN activity e
  ON e.machine_id = s.machine_id
 AND e.process_id = s.process_id
 AND e.activity_type = 'end'
WHERE s.activity_type = 'start'
GROUP BY s.machine_id
ORDER BY s.machine_id;

Key concepts

Self join
Self-join on composite key
AVG
ROUND
CASE WHEN

Average Process Time Per Machine