Calculate Average Process Time per Machine with MySQL
Intuition\n Describe your first thoughts on how to solve this problem. \nWe need to calculate the average time each machine takes to complete a process. For thi
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nWe need to calculate the average time each machine takes to complete a process. For this, we can find find the time taken for each process on a machine and then calculate the average time for each machine.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n1. Select specific columns from the table.\n    > `SELECT` \u2192 This command retrieves data from a database\n\n    > `AS` \u2192 This command renames a column with an alias (temporary name). In most database languages, we can skip the `AS` keyword and get the same result\n\n2. Count the number of unique processes that were run on each machine.\n    > `COUNT()` \u2192 This function returns the number of rows\n\n    > `DISTINCT` \u2192 This statement returns only distinct (different) values\n\n3. Calculate the total time it takes to complete each process on a machine and round this to the specified number of decimal places. \n\n    > `ROUND(number, decimal)`  \u2192 This function rounds a `number` to a specified number of `decimal` places\n\n    > `SUM()` \u2192 This function returns the total sum of a numeric column\n\n    > `IF(condition, value_if_true, value_if_false)` \u2192 This function returns a value if a `condition` is `TRUE`, or another value if it is `FALSE`\n    By negating the `start` time, we ensure that we get the accurate duration of the process on a machine (it subtracts the `start` time from the `end` time).\n\n4. Group and arrange the results based on specific columns.\n\n    > `GROUP BY` \u2192 This command groups rows that have the same values into summary rows, typically to perform aggregate functions on them\n\n    > `ORDER BY` \u2192 This command sorts the result-set in ascending (smallest to largest) order\n\n# Complexity\n- Time complexity: $O(n)$\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n`n` is the number of rows in the table. The query needs to examine each row to calculate the average processing time for each machine.\n\n# Code\n```\nSELECT \n    machine_id, \n    ROUND(SUM(IF(activity_type=\'start\', -timestamp, timestamp)) / COUNT(DISTINCT process_id), 3) AS processing_time\nFROM Activity\nGROUP BY machine_id\nORDER BY machine_id\n```
['MySQL']