Average Time of Process per Machine - Problem

You have a table called Activity that tracks factory machine activities with the following structure:

  • machine_id: ID of the machine
  • process_id: ID of the process running on the machine
  • activity_type: Either 'start' or 'end'
  • timestamp: Time in seconds when the activity occurred

Goal: Calculate the average time each machine takes to complete a process. The process time is calculated as end timestamp - start timestamp, and the average is the total time divided by the number of processes.

Return the machine_id and processing_time (rounded to 3 decimal places) for each machine.

Table Schema

Activity
Column Name Type Description
machine_id PK int ID of the machine
process_id PK int ID of the process running on the machine
activity_type PK enum Either 'start' or 'end'
timestamp float Time in seconds when activity occurred
Primary Key: (machine_id, process_id, activity_type)
Note: Each (machine_id, process_id) pair has exactly one 'start' and one 'end' record

Input & Output

Example 1 — Basic Machine Process Times
Input Table:
machine_id process_id activity_type timestamp
0 0 start 0.712
0 0 end 1.52
0 1 start 3.14
0 1 end 4.512
1 0 start 0.55
1 0 end 1.55
1 1 start 0.43
1 1 end 1.42
Output:
machine_id processing_time
0 1.09
1 0.995
💡 Note:

Machine 0 has two processes: Process 0 takes (1.520 - 0.712) = 0.808 seconds, Process 1 takes (4.512 - 3.140) = 1.372 seconds. Average: (0.808 + 1.372) / 2 = 1.090 seconds. Machine 1 has Process 0 taking (1.550 - 0.550) = 1.000 seconds and Process 1 taking (1.420 - 0.430) = 0.990 seconds. Average: (1.000 + 0.990) / 2 = 0.995 seconds.
Example 2 — Single Process per Machine
Input Table:
machine_id process_id activity_type timestamp
0 0 start 1
0 0 end 3
1 0 start 2
1 0 end 5
Output:
machine_id processing_time
0 2
1 3
💡 Note:

Each machine has only one process. Machine 0's process takes (3.0 - 1.0) = 2.000 seconds. Machine 1's process takes (5.0 - 2.0) = 3.000 seconds. Since there's only one process per machine, the average equals the single process time.

Constraints

  • 1 ≤ machine_id ≤ 100
  • 1 ≤ process_id ≤ 100
  • activity_type is either 'start' or 'end'
  • 1 ≤ timestamp ≤ 1000
  • Each (machine_id, process_id) pair has exactly one 'start' and one 'end' record

Visualization

Tap to expand
Average Time of Process per Machine INPUT machine process type time 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 Activity Log Table Machine 0 2 processes Machine 1 1 process ALGORITHM STEPS 1 Self-Join on Activity Match start/end by machine and process_id 2 Calculate Duration end_time - start_time for each process 3 Group by Machine GROUP BY machine_id 4 Compute Average AVG(duration) per machine Round to 3 decimals Machine 0: (1.520-0.712)+(4.120-3.140) = 0.808 + 0.980 = 1.788 AVG = 1.788/2 = 0.894 Machine 1: (1.550-0.550)/1 = 1.000 FINAL RESULT machine_id avg_time 0 0.894 1 1.000 Processing Time Comparison M0: 0.894s M1: 1.000s Query Result OK - 2 rows SELECT machine_id, ROUND(AVG(duration),3) Key Insight: Use a self-join to pair each 'start' row with its matching 'end' row (same machine_id and process_id). Calculate the time difference (end - start) for each process, then use AVG() with GROUP BY machine_id to get the average processing time per machine. Time complexity: O(n) where n is number of activities. TutorialsPoint - Average Time of Process per Machine | Optimal Solution

Related Problems

Asked in
Meta 28 Amazon 22 Google 15
28.5K Views
High Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen