Average Matched Process Durations for Each Machine

### Problem Return machine_id and processing_time, the average process duration per machine rounded to three decimal places. A duration is its end timestamp minus its start timestamp for the same machine_id and process_id. Every process has exactly the start/end pair described by the source; equal timestamps and zero duration are allowed. All timestamps are finite. Interpret each reading as the shortest decimal value that converts back to the same FLOAT. Row order is unrestricted. ### Tables Table: `Activity` ```text +----------------+---------+ | Column Name | Type | +----------------+---------+ | machine_id | int | | process_id | int | | activity_type | enum | | timestamp | float | +----------------+---------+ The table shows the user activities for a factory website. (machine_id, process_id, activity_type) is the primary key (combination of columns with unique values) of this table. machine_id is the ID of a machine. process_id is the ID of a process running on the machine with ID machine_id. activity_type is an ENUM (category) of type ('start', 'end'). timestamp is a float representing the current time in seconds. 'start' means the machine starts the process at the given timestamp and 'end' means the machine ends the process at the given timestamp. The `start` timestamp will always be less than or equal to the `end` timestamp for every `(machine_id, process_id)` pair. It is guaranteed that each (machine_id, process_id) pair has a 'start' and 'end' timestamp. ``` ### Example ```text Activity (machine_id,process_id,activity_type,timestamp): (1,7,start,1.250), (1,7,end,2.750), (1,8,start,4.000), (1,8,end,4.000), (2,7,start,3.000), (2,7,end,5.500) Output (machine_id,processing_time): (1,0.750), (2,2.500) ``` ### What a Strong Answer Covers - Match each start with its corresponding end for the same machine and process. - Average the durations per machine, including zero durations, and round to three decimal places. ### Follow-up Questions - How would you handle processes with missing start or end records? - How would you report both average and longest duration per machine?