Solution using AVG and CASE - MySQL
Explanation:\nWhat we actually want to do is to compute the difference corresponding to each machine and process pair and then take average. \n1. Instead of dif
Explanation:\nWhat we actually want to do is to compute the difference corresponding to each machine and process pair and then take average. \n1. Instead of difference, we can just add negative timestamp of the \'start\' entry and positive timestamp for \'end\' entry. It will effectively be the same.\n2. After doing this, when you take average, the total number of entries double because value corresponding to end and start are considered separately. So, multiply the average with a 2.\n3. Finally, the average we want is for all the processes corresponding to a machine. So, we just have to group by machine. Which process the start and end timestamps belong to is not required because we are just finding the average.\n\n```\nSELECT\n    a.machine_id, ROUND(AVG(CASE \n                   WHEN a.activity_type = \'end\' THEN a.timestamp\n                   WHEN a.activity_type = \'start\' THEN -a.timestamp\n                  END)*2, 3) as processing_time\nFROM\n    Activity a\nGROUP BY\n    a.machine_id\n```
[]