1661. Average Time of Process per Machine

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

My SQL

SELECT 
    machine_id,
    ROUND(AVG(endTime - startTime), 3) AS processing_time
FROM (
    SELECT 
        machine_id,
        process_id,
        MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS startTime,
        MAX(CASE WHEN activity_type = 'end' THEN timestamp END) AS endTime
    FROM Activity
    GROUP BY machine_id, process_id
) AS ProcessTimes
GROUP BY machine_id;

서브 쿼리를 통해서 start, end 를 가져오고 요구사항대로 계산한다.

Oracle

SELECT 
    machine_id,
    ROUND(AVG(endTime - startTime), 3) AS processing_time
FROM (
    SELECT 
        machine_id,
        process_id,
        MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS startTime,
        MAX(CASE WHEN activity_type = 'end' THEN timestamp END) AS endTime
    FROM Activity
    GROUP BY machine_id, process_id
) ProcessTimes
GROUP BY machine_id;

대동소이