1661. Average Time of Process per Machine

• There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
  
  The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
  
  The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
  
  Return the result table in any order.
  The result format is in the following example.

SELECT a1.machine_id
     , ROUND(AVG(a2.timestamp-a1.timestamp),3) AS  processing_time
FROM activity a1
JOIN activity a2
ON a1.machine_id = a2.machine_id
WHERE a1.activity_type='start' AND a2.activity_type='end'
GROUP BY a1.machine_id

 

①

FROM activity a1

JOIN activity a2

ON a1.machine_id = a2.machine_id

 

 

# activity 테이블을 self join 

# on 절의 조건을 모두 취합하여 join

 

 

 

② WHERE a1.activity_type='start' AND a2.activity_type='end'

# start 와 end 타입이 같을 때 를 조건으로 준다.

# 동일한 machine_id 로 취합하고 start 타입과 end 타입이 같을 때 출력

 

 

③

SELECT a1.machine_id

     , ROUND(AVG(a2.timestamp-a1.timestamp),3) AS  processing_time

 

# machine_id 별로 end 타입인 timestamp 에서 start 타입의 timestamp 를 뺀 평균을 만들어준다

 

 

 

---

참고

https://leetcode.com/problems/average-time-of-process-per-machine/