✅[MySQL] Simple and Clean, beats 88%✅
Average Time of Process per Machine\n### Please upvote if you find this helpful. \u270C\n\n\n## Intuition\n\nThe problem requires us to find the average time ea
# Average Time of Process per Machine\n### Please upvote if you find this helpful. \u270C\n<img src="https://assets.leetcode.com/users/images/b8e25620-d320-420a-ae09-94c7453bd033_1678818986.7001078.jpeg" alt="Cute Robot - Stable diffusion" width="200"/>\n\n## Intuition\n\nThe problem requires us to find the average time each machine takes to complete a process in a factory. We are given a table that records the start and end timestamps of each process for each machine. To calculate the average time, we need to subtract the start timestamp from the end timestamp for each process and then find the average time for all processes on each machine.\n\n## Approach\n\nTo solve the problem, we can use the following approach:\n\n1. Join the `Activity` table with itself, denoting the first occurrence as `a` and the second occurrence as `b`.\n2. Use the `JOIN` condition to match the rows with the same `machine_id`, `process_id`, and activity types \'start\' and \'end\'.\n3. Calculate the time taken for each process by subtracting the start timestamp (`a.timestamp`) from the end timestamp (`b.timestamp`).\n4. Use the `GROUP BY` clause to group the results by `machine_id`.\n5. Apply the `AVG` function on the time taken for each machine to calculate the average time.\n6. Round the average time to 3 decimal places using the `ROUND` function.\n7. Select the `machine_id` and the rounded average time as `processing_time`.\n\nThe SQL query for the above approach is as follows:\n\n```sql\nSELECT a.machine_id, ROUND(AVG(b.timestamp - a.timestamp), 3) AS processing_time\nFROM Activity a\nJOIN Activity b ON a.machine_id = b.machine_id AND a.process_id = b.process_id AND a.activity_type = \'start\' AND b.activity_type = \'end\'\nGROUP BY a.machine_id;\n
['MySQL']