Skip to content
>_sqlbuddy
easyJOINsAggregationSelf Join

Average Process Time Per Machine

Compute each machine's average time to complete a process.

Start practice

Prompt

Average Process Time Per Machine

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_idprocess_idactivity_typetimestamp
00start0.712
00end1.520
01start3.140
01end4.120
10start0.500
10end2.000

Expected result:

machine_idprocessing_time
00.894
11.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.

Expected concepts

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

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.