CTE Approach
Intuition\nI thought I can get use of LAG() function to solve the problem. However, it would be for an ideal structured and ordered dataset.\n\n# Approach\nThen
# Intuition\nI thought I can get use of LAG() function to solve the problem. However, it would be for an ideal structured and ordered dataset.\n\n# Approach\nThen I considered using CTE!\n\n# Complexity\n- Time complexity:\n\n- Space complexity:\n\n\n# Code\n```\n-- Write your PostgreSQL query statement below\nWITH time_calc AS (\n    SELECT\n        machine_id,\n        process_id,\n        MAX(CASE WHEN activity_type =\'end\' THEN timestamp END) -\n        MIN(CASE WHEN activity_type =\'start\' THEN timestamp END) AS time_spent\n    FROM\n        Activity\n    GROUP BY\n        machine_id, process_id\n)\n\nSELECT \n    machine_id, \n    ROUND(AVG(time_spent)::numeric, 3) AS processing_time\nFROM \n    time_calc\nGROUP BY machine_id\nORDER BY machine_id;\n```
['PostgreSQL']