LeetCode 1661: Average Time of Process per Machine

Explanation:

To solve this problem, we need to calculate the average time each machine takes to complete a process. This can be done by grouping the activities by machine_id and process_id, then calculating the processing time for each process and finally finding the average processing time for each machine.

1. Group the activities by machine_id and process_id.
2. Calculate the processing time for each process by subtracting the 'start' timestamp from the 'end' timestamp.
3. Average the processing times for each machine.

Time Complexity: O(n) where n is the number of rows in the Activity table. Space Complexity: O(n) for storing the grouped activities.

:

# Write your Java solution here
SELECT machine_id, ROUND(AVG(end_time - start_time), 3) AS processing_time
FROM (
    SELECT machine_id, process_id, MIN(timestamp) AS start_time, MAX(timestamp) AS end_time
    FROM Activity
    GROUP BY machine_id, process_id
) AS temp
GROUP BY machine_id;