[LeetCode] - 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.

 

 

문제 풀이 

SELECT machine_id
	,  ROUND(SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestampe END) / COUNT(DISTINCT process_id),3) processing_time 
FROM Activity 
GROUP BY machine_id

 

1. CASE 문을 활용하여 activity_type이 end인 경우 timestamp 값을 유지하고, activity_type이 start인 경우 -timestamp 값을 가지게 한다. 

 

2. SUM과 COUNT를 이용하여 평균을 구한다. 

 

3. GROUP BY를 이용해서 macine_id 별로 평균 값을 구하게 한다. 

 

오답이유 

CASE WHEN을 활용해야 한다는 것을 몰랐다. 더 어렵게 WINDOW 문을 이용해서 풀려고 했는데 잘 되지 않았다. 단순하게 생각하시오...