SQL: Calculating Average Process Duration per Machine
\n# Code\n\n# Write your MySQL query statement below\nSELECT \n    a1.machine_id, \n    round(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time\nFROM \n  
\n# Code\n```\n# Write your MySQL query statement below\nSELECT \n    a1.machine_id, \n    round(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time\nFROM \n    activity a1\nJOIN \n    activity a2 ON a1.machine_id = a2.machine_id \n                AND a1.process_id = a2.process_id\n                AND a1.activity_type = \'start\'\n                AND a2.activity_type = \'end\'\nGROUP BY \n    a1.machine_id;\n```\n# Approach\n<!-- Describe your approach to solving the problem. -->\nThe solution involves performing a self-join on the activity table, matching each \'start\' record with its corresponding \'end\' record based on machine_id and process_id. This join allows us to calculate the duration for each process by subtracting the \'start\' timestamp from the \'end\' timestamp. Once we have these durations, we can then use SQL\'s AVG() function to find the average duration per machine, and ROUND() to format this average to three decimal places for precision and readability.\n\n# Complexity\n- Time complexity: $$O(n^2)$$\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity: $$O(n)$$\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n![LeetCode-Upvote.png](https://assets.leetcode.com/users/images/a38fd607-8d99-444f-b88b-5b75542ff00f_1702963425.2738767.png)\n
['MySQL']