00:00:00

Description

Editorial

Average Process Duration by Machine

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

[{"machine_id":1,"process_id":100,"activity_type":"start","timestamp":0.712},{"machine_id":1,"process_id":100,"activity_type":"end","timestamp":1.52},{"machine_id":1,"process_id":101,"activity_type":"start","timestamp":3.14},{"machine_id":1,"process_id":101,"activity_type":"end","timestamp":4.12},{"machine_id":2,"process_id":200,"activity_type":"start","timestamp":0.55},{"machine_id":2,"process_id":200,"activity_type":"end","timestamp":1.55},{"machine_id":2,"process_id":201,"activity_type":"start","timestamp":0.43},{"machine_id":2,"process_id":201,"activity_type":"end","timestamp":1.42},{"machine_id":3,"process_id":300,"activity_type":"start","timestamp":4.1},{"machine_id":3,"process_id":300,"activity_type":"end","timestamp":4.512},{"machine_id":3,"process_id":301,"activity_type":"start","timestamp":2.5},{"machine_id":3,"process_id":301,"activity_type":"end","timestamp":5}]

Loading problem...

101

00:00:00

Description

Editorial

Average Process Duration by Machine

EASY15 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

activity =

Average Process Duration by Machine

Hints

Average Process Duration by Machine

Hints