https://www.youtube.com/watch?v=s7-xXJGdBs8

CREATE TABLE Activity (
    machine_id INT,
    process_id INT,
    activity_type VARCHAR(10),
    timestamp DECIMAL(10,3)
);

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.550),
(1, 0, 'end', 1.550),
(1, 1, 'start', 0.430),
(1, 1, 'end', 1.420),
(2, 0, 'start', 4.100),
(2, 0, 'end', 4.512),
(2, 1, 'start', 2.500),
(2, 1, 'end', 5.000);

LAG()

WITH CTE AS (
SELECT 
	*,
    LAG(timestamp) OVER (PARTITION BY machine_id,process_id ORDER BY timestamp) AS lg
FROM ac )

SELECT machine_id,AVG(timestamp - lg) AS Avg_time
FROM CTE
WHERE lg is NOT NULL
GROUP BY machine_id