Average Process Duration by Machine

Easy

A manufacturing observability system records process lifecycle events from multiple machines. Table: activity machine_id: machine identifier process_id: process identifier scoped to machine_id activity_type: event type, either "start" or "end" timestamp: event time in seconds Data guarantees: (machine_id, process_id, activity_type) is unique. Every (machine_id, process_id) pair has exactly one start and one end event. For each process, the start timestamp is strictly earlier than the end timestamp. Metric definitions: process_duration = end_timestamp - start_timestamp processing_time (per machine) = average process_duration for that machine Task: Return the average process duration per machine. Output requirements: Return exactly two columns: machine_id processing_time processing_time must be rounded to 3 decimal places. Result rows may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

activity:
| machine_id | process_id | activity_type | timestamp |
|------------|------------|---------------|-----------|
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |

Output

[
  {"machine_id":0,"processing_time":0.894},
  {"machine_id":1,"processing_time":0.995},
  {"machine_id":2,"processing_time":1.456}
]

Explanation

For each machine, compute duration per process and then take the arithmetic mean of those durations.

Example 2

Input

activity:
| machine_id | process_id | activity_type | timestamp |
|------------|------------|---------------|-----------|
| 5          | 11         | start         | 10.100    |
| 5          | 11         | end           | 11.000    |
| 5          | 12         | start         | 12.000    |
| 5          | 12         | end           | 13.500    |
| 5          | 13         | start         | 15.250    |
| 5          | 13         | end           | 15.750    |

Output

[
  {"machine_id":5,"processing_time":0.967}
]

Explanation

Durations are [0.900, 1.500, 0.500], whose average is 0.966666..., rounded to 0.967.

Example 3

Input

activity:
| machine_id | process_id | activity_type | timestamp |
|------------|------------|---------------|-----------|
| 3          | 30         | start         | 1.000     |
| 3          | 30         | end           | 2.000     |
| 7          | 70         | start         | 1.100     |
| 7          | 70         | end           | 1.700     |
| 7          | 71         | start         | 2.400     |
| 7          | 71         | end           | 4.000     |

Output

[
  {"machine_id":3,"processing_time":1.0},
  {"machine_id":7,"processing_time":1.1}
]

Explanation

Machine 3 has one process with duration 1.0. Machine 7 has durations 0.6 and 1.6, so average is 1.1.

Constraints

0 <= number of rows in activity <= 300000
(machine_id, process_id, activity_type) is unique
1 <= machine_id <= 1000000000 when rows exist
1 <= process_id <= 1000000000 when rows exist
activity_type is one of: start, end
0 <= timestamp <= 1000000000
Every (machine_id, process_id) has one start and one end
For every process, start timestamp < end timestamp
Return exactly two columns: machine_id, processing_time
Round processing_time to exactly 3 decimal places
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: machine_id, processing_time

activity: | machine_id | process_id | activity_type | timestamp | |------------|------------|---------------|-----------| | 0 | 0 | start | 0.712 | | 0 | 0 | end | 1.520 | | 0 | 1 | start | 3.140 | | 0 | 1 | end | 4.120 | | 1 | 0 | start | 0.550 | | 1 | 0 | end | 1.550 | | 1 | 1 | start | 0.430 | | 1 | 1 | end | 1.420 | | 2 | 0 | start | 4.100 | | 2 | 0 | end | 4.512 | | 2 | 1 | start | 2.500 | | 2 | 1 | end | 5.000 |

activity: | machine_id | process_id | activity_type | timestamp | |------------|------------|---------------|-----------| | 5 | 11 | start | 10.100 | | 5 | 11 | end | 11.000 | | 5 | 12 | start | 12.000 | | 5 | 12 | end | 13.500 | | 5 | 13 | start | 15.250 | | 5 | 13 | end | 15.750 |

activity: | machine_id | process_id | activity_type | timestamp | |------------|------------|---------------|-----------| | 3 | 30 | start | 1.000 | | 3 | 30 | end | 2.000 | | 7 | 70 | start | 1.100 | | 7 | 70 | end | 1.700 | | 7 | 71 | start | 2.400 | | 7 | 71 | end | 4.000 |