LeetCode: 1661. Average Time of Process per Machine

Lets see my plan:

Image A

Here T1 (left table) T2(Right table)

Select 

* 

from Activity t1 

Join Activity t2 

on t1.machine_id = t2.machine_id 

and t1.process_id = t2.process_id 

and t1.activity_type='start'   

and t2.activity_type='end'  

 

If you run this code, you will get a result like Image A

Now, following our question requirement, calculate:

avg(t2.timestamp — t1.timestamp) # T2 — T1 , then Average of it

So, the Final Code:

Select 

t1.machine_id,  

round(avg(t2.timestamp-t1.timestamp), 3) as processing_time  

from Activity t1 

Join Activity t2 

on t1.machine_id = t2.machine_id 

and t1.process_id = t2.process_id 

and t1.activity_type='start'   

and t2.activity_type='end'  

group by t1.machine_id  

Another way,

and t1.activity_type='start'    and t2.activity_type='end'
# we can use
and a1.timestamp<a2.timestamp; # works same

Below is the top solution I got from LeetCode:

select machine_id, round(avg(time_lapse), 3) as processing_time
from
(select machine_id, process_id,
max(timestamp) over (partition by machine_id, process_id) 
-
min(timestamp) over (partition by machine_id, process_id) 
as time_lapse from Activity) temp
group by machine_id