2 SOLUTION WITH [ LEAD() OVER() ] OR SIMPLE SUM AND COUNT ( SQL SERVER )
Intuition\r\n Describe your first thoughts on how to solve this problem. \r\n\r\n# Approach\r\n Describe your approach to solving the problem. \r\n\r\n# Complex
# Intuition\r\n<!-- Describe your first thoughts on how to solve this problem. -->\r\n\r\n# Approach\r\n<!-- Describe your approach to solving the problem. -->\r\n\r\n# Complexity\r\n- Time complexity:\r\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\r\n\r\n- Space complexity:\r\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\r\n\r\n# Code\r\n```\r\n/* Write your T-SQL query statement below */\r\n\r\n\r\n--SOLUTION WITH [ LEAD() OVER() ]\r\n\r\nWITH CTE AS (\r\n        SELECT\r\n            machine_id,\r\n            timestamp,\r\n            lead(timestamp) OVER(PARTITION BY machine_id,process_id ORDER BY activity_type DESC) nextActive\r\n        FROM\r\n            Activity\r\n)\r\nSELECT\r\n    machine_id,\r\n    ROUND(SUM(nextActive - timestamp) / COUNT( machine_id),3) processing_time\r\nFROM\r\n    CTE\r\nWHERE\r\n    nextActive IS NOT NULL\r\nGROUP BY\r\n    machine_id\r\n```\r\n\r\n\r\n--SOLUTION WITH SIMPLE SUM AND COUNT\r\n\r\nWITH CTE AS (SELECT\r\n    machine_id,\r\n    SUM(CASE WHEN activity_type = \'start\' THEN timestamp ELSE NULL END) SUMSTART,\r\n    SUM(CASE WHEN activity_type = \'end\' THEN timestamp ELSE NULL END) SUMEND,\r\n    COUNT(machine_id) / 2 CountM\r\nFROM\r\n    Activity\r\nGROUP BY \r\n    machine_id\r\n)\r\nSELECT\r\n    machine_id,\r\n    ROUND((SUMEND - SUMSTART) / CountM,3) processing_time\r\nFROM\r\n    CTE
['MS SQL Server']