USING CASE | SUM | COUNT | DISTINCT
Intuition\nCASE for seperating start & end values from activity table.\nSUM for finding the difference.\nCOUNT * DISTINCT for finding average.\nROUND to round o
# Intuition\nCASE for seperating start & end values from activity table.\nSUM for finding the difference.\nCOUNT * DISTINCT for finding average.\nROUND to round off values.\n\n# Approach\nIn order to take average processing time, first step is to seperate the start & end time which is done using CASE.After finding the difference using CASE statement, to find average divide the difference with DISTINCT process_id\'s which need to be grouped by machine_id.\nFinal step is to reound off the values.\n\n\n# Code\n```\n/* Write your T-SQL query statement below */\nSELECT machine_id,\nROUND((SUM(CASE \nWHEN activity_type = \'end\' THEN timestamp\nEND) -\nSUM(CASE \nWHEN activity_type = \'start\' THEN timestamp\nEND)) / COUNT(DISTINCT process_id),3)\nAS processing_time\nFROM Activity\nGROUP BY machine_id\n\n\n\n\n\n\n```
['MS SQL Server']