Calculate Process Duration with Activity Pair Joins in SQL

Question

Given an `activity` table (machine_id, process_id, activity_type, timestamp), find the average processing time for each `machine_id` rounded to 3 decimal places.

Accepted Answer

Optimal Solution:
SELECT 
  a1.machine_id,
  ROUND(AVG(a2.timestamp - a1.timestamp)::numeric, 3) AS processing_time
FROM activity a1
JOIN activity a2 
  ON a1.machine_id = a2.machine_id 
 AND a1.process_id = a2.process_id
 AND a1.activity_type = 'start'
 AND a2.activity_type = 'end'
GROUP BY a1.machine_id
ORDER BY a1.machine_id ASC;

Technical Explanation:
By joining the activity table to itself on `machine_id` and `process_id` matching 'start' with 'end', we calculate `a2.timestamp - a1.timestamp` per process, then take the `AVG()` per machine.

process_id	activity_type	timestamp
0	start	0.712
0	end	1.52
1	start	3.14
1	end	4.12