문제 86번

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.

(링크)

초안

SELECT
    a.machine_id
    , a.process_id
#    , timestampdiff(second, b.timestamp, a.timestamp) as 'difftime'
    , b.timestamp - a.timestamp as 'difftime'
FROM (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE 
        activity_type = 'start'
    ) a
    INNER JOIN
    (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE 
        activity_type = 'end'
    ) b
        ON a.machine_id = b.machine_id
GROUP BY
    a.process_id

difftime이 음수가 나왔다

2안

SELECT
    *
FROM (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE
        activity_type = 'start'
    ) s
    INNER JOIN (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE
        activity_type = 'end'
    ) e
        ON s.machine_id = e.machine_id 
            AND s.process_id = e.process_id

구해야 할 것

1) 동일 machine_id, process_id일 때 end와 start의 시간 차이 (단위 second)
activity_type이 start인 값들과 activity_type이 end인 값들을 따로 분리해서 INNER JOIN을 한다
조인 조건을 machine_id, process_id로 한다
start와 end의 값들의 차를 구한다

2) 1의 평균

최종안

SELECT
    s.machine_id
#    , s.process_id
    , round(avg(e.timestamp - s.timestamp), 3) as 'processing_time'
FROM (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE
        activity_type = 'start'
    ) s
    INNER JOIN (
    SELECT
        machine_id
        , process_id
        , timestamp
    FROM
        activity
    WHERE
        activity_type = 'end'
    ) e
        ON s.machine_id = e.machine_id 
            AND s.process_id = e.process_id
GROUP BY
    machine_id
;

다른 사람의 모범 답안

# https://leetcode.com/problems/average-time-of-process-per-machine/solutions/5795248/easy-solution-with-explanation
WITH ProcessTimes AS (
    SELECT 
        a.machine_id,
        a.process_id,
        MAX(CASE WHEN a.activity_type = 'start' THEN a.timestamp END) AS start_time,
        MAX(CASE WHEN a.activity_type = 'end' THEN a.timestamp END) AS end_time
    FROM 
        Activity a
    GROUP BY 
        a.machine_id,
        a.process_id
),
Durations AS (
    SELECT 
        machine_id,
        (end_time - start_time) AS duration
    FROM 
        ProcessTimes
)
SELECT 
    machine_id,
    ROUND(AVG(duration), 3) AS processing_time
FROM 
    Durations
GROUP BY 
    machine_id;

문제 87번

Employee Bonus

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

(링크)

초안

SELECT
    e.name
    , b.bonus
FROM
    employee e
    LEFT JOIN 
    (
    SELECT
        empId
        , bonus
    FROM
        bonus
    WHERE
        bonus < 1000
    ) b
        ON e.empId = b.empId
;

처음에는 문제를 잘못 이해했었다

Bonus 테이블에 ID가 없다는 것은 보너스가 사실 0원이라는 의미
즉 Brad, John은 조건에 맞지만, Thomas는 보너스가 2000원으로 문제에서 요구하는 바에 포함되지 않는다

그렇다면 LEFT JOIN으로 날먹하려던 의도가 통하지 않는다
기존처럼 JOIN을 하기 전에 미리 걸러내면 안 되고,
일단 JOIN은 진행한 다음에, 조건문에서 “bonus가 1000 미만이거나 IS NULL 이거나”로 조건을 달아줘야 할 듯

최종안

SELECT
    e.name
    , b.bonus
FROM
    employee e
    LEFT JOIN 
    bonus b
        ON e.empId = b.empId
WHERE
    (bonus < 1000)
    OR (bonus IS NULL)
;