Easy solution with Window function 100% (no joins)
First create the query to determine the delta table. This can be done in a simple way with a window function. First order by activity within a partition, then s
First create the query to determine the delta table. This can be done in a simple way with a window function. First order by activity within a partition, then subtract the lagged value (previous row) so that for the activity = \'end\' you will have the value you need and for the \'start\' you will have null values\n```\nselect machine_id, timestamp - lag(timestamp,1) over (partition by machine_id, process_id order by activity_type) as delta \nfrom Activity as DeltaTabl\n```\n\nNow all you have left to do is simply select the non-null entries and round, so the final query should look like: \n```\nselect machine_id, round(avg(delta),3) as processing_time from \n(select machine_id, timestamp - lag(timestamp,1) over (partition by machine_id, process_id order by activity_type) as delta \nfrom Activity as DeltaTable) \nas result where delta is not null group by machine_id\n```\n
[]