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.494
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 MachineInput: Activity Recordsmachine_idprocess_idactivity_typetimestamp00start0.71200end1.52001start3.14001end4.51210start0.55010end1.550Self-Join+ AVGOutput: Average Timesmachine_idprocessing_time01.49410.995Machine 0 Process Times: 0.808s, 1.372s → Average: 1.090sMachine 1 Process Times: 1.000s → Average: 1.000s
Understanding the Visualization
1
Input
Activity table with start/end records
2
Pair
Match start and end for same machine-process
3
Average
Calculate average time per machine
Key Takeaway
🎯 Key Insight: Use self-join to pair start/end records, then group by machine_id for average calculations

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