[leetcode]Average Time of Process per Machine

문제

https://leetcode.com/problems/average-time-of-process-per-machine/description/?envType=study-plan-v2&envId=top-sql-50

코드

WITH start_t AS(
    SELECT *
    FROM Activity
    WHERE activity_type = 'start'
),
end_t AS(
    SELECT *
    FROM Activity
    WHERE activity_type='end'
)

SELECT
    start_t.machine_id,
    ROUND((SUM(end_t.timestamp-start_t.timestamp)/COUNT(start_t.process_id)),3) AS processing_time 
FROM
    start_t 
    INNER JOIN end_t
        ON start_t.machine_id=end_t.machine_id AND start_t.process_id=end_t.process_id
GROUP BY
   start_t.machine_id

후기

CTE로 timestamp를 start와 end로 구분지어 피봇하여서 나눠줌.
이후 산술을 하는데 처음에 COUNT(*) 했다가 안되길래 COUNT(start_t.process_id)이렇게 바꿈.
아마 COUNT(start_t.process_id)이렇게 하면 그룹화 되기 전에 수를 계산하는 듯