find the average processing time by machine for all processes

avg_machine_process_time.sql

-- Write your PostgreSQL query statement below
/* 
Lets first do a table scan to get every  process_id's time delta 
*/
with process_time_delta as (
    Select
        machine_id,
        process_id,
        activity_type,
        timestamp,
        LAG(timestamp, 1) OVER (
            Partition by machine_id, process_id
            ORDER by timestamp asc
        ) as start_timestamp
    From Activity
)
select
    a.machine_id,
    round(avg(a.timestamp - p.start_timestamp)::numeric, 3) as processing_time
from Activity a
inner join process_time_delta p
on p.machine_id = a.machine_id
and p.process_id = a.process_id
where a.activity_type = 'end'    /* only need the end activity timestamp */
group by 1