You're given a table called Activity that logs process events for a factory's website. Each machine runs the same number of processes, and every process has exactly two entries in the table: one for 'start' and one for 'end', recorded with timestamps.
The task is to calculate the average time each machine takes to complete a process. The time for each individual process is computed as the difference between the 'end' and 'start' timestamps. To get the average time for each machine, you'll:
- Pair up the
'start' and 'end' entries for each (machine_id, process_id) to compute the duration for that process. - Group all processes by
machine_id and calculate the average of their durations. - Round the average processing time to three decimal places.
- Return a result table with two columns:
machine_id and processing_time.
Example SQL Query:
sql
SELECT
machine_id,
ROUND(AVG(end_time - start_time), 3) AS processing_time
FROM (
SELECT
machine_id,
process_id,
MAX(CASE WHEN activity_type = 'start' THEN timestamp END) AS start_time,
MAX(CASE WHEN activity_type = 'end' THEN timestamp END) AS end_time
FROM Activity
GROUP BY machine_id, process_id
) AS process_durations
GROUP BY machine_id;
This query ensures that the durations for each process are calculated accurately and then averaged per machine. The result can be returned in any order.