5 different methods(basically sum/count  or avg(difference)
/#window function\nselect\nmachine_id,\nround(avg(time_diff),3) processing_time\nfrom\n(select\nmachine_id,\ntimestamp - lag(timestamp,1)over(partition by machi
/*#window function\nselect\nmachine_id,\nround(avg(time_diff),3) processing_time\nfrom\n(select\nmachine_id,\ntimestamp - lag(timestamp,1)over(partition by machine_id, Process_id order by timestamp) as time_diff\nfrom activity) a\nwhere time_diff is not null\ngroup by 1\n*/\n/*#most simple way with temp table\nwith starttime as (select machine_id, timestamp as starttime from activity where activity_type =\'start\'),\n\nendtime as (select machine_id, timestamp as endtime from activity where activity_type =\'end\')\n\nselect s.machine_id,\nround(avg(endtime-starttime),3) as processing_time\nfrom starttime s\ninner join endtime e on s.machine_id=e.machine_id\ngroup by 1\n*/\n/*#sum(if())\nselect machine_id,\nround(sum(if(activity_type=\'start\', -timestamp, timestamp)) / count(distinct process_id),3) as processing_time\nfrom activity\ngroup by 1\n*/\n/*case statement\nselect\nmachine_id,\nround((endtime-starttime)/cnt_pid, 3) as processing_time\nfrom\n(\nselect machine_id,\nsum(case when activity_type=\'start\' then timestamp end) as starttime,\nsum(case when activity_type=\'end\' then timestamp end) as endtime,\ncount(distinct process_id) as cnt_pid\nfrom activity\ngroup by 1) a\n*/\n
[]