New Method || Using LAG() function (without Joins) || MySQL ||
IntuitionThe LAG() function allows you to create a new column that references a previous row from another column. We can use this function to get the end and st
# Intuition
<!-- Describe your first thoughts on how to solve this problem. -->
The LAG() function allows you to create a new column that references a previous row from another column. We can use this function to get the end and start times in the same row, thereby avoiding the need for self-joins in the table. Before using the LAG() function, ensure that the machine_id, process_id, and activity_type columns are in ascending order, as illustrated in the problem example.

For example -- We should make sure that the arrangement is not like THIS
| machine_id | process_id | activity_type | timestamp |
| ---------- | ---------- | ------------- | --------- |
| 0          | 1          | start         | 18.891    |
| 1          | 0          | end           | 81.874    |
| 0          | 0          | start         | 37.019    |
| 0          | 1          | end           | 38.098    |
| 1          | 0          | start         | 25.135    |
| 1          | 1          | start         | 23.355    |
| 0          | 0          | end           | 40.222    |
| 1          | 1          | end           | 90.302    |

BUT rather like THIS
| machine_id | process_id | activity_type | timestamp |
| ---------- | ---------- | ------------- | --------- |
| 0          | 0          | start         | 37.019    |
| 0          | 0          | end           | 40.222    |
| 0          | 1          | start         | 18.891    |
| 0          | 1          | end           | 38.098    |
| 1          | 0          | start         | 25.135    |
| 1          | 0          | end           | 81.874    |
| 1          | 1          | start         | 23.355    |
| 1          | 1          | end           | 90.302    |

# Approach
<!-- Describe your approach to solving the problem. -->
1. I created a CTE table (prev_table) and used the LAG() function with the ORDER BY clause within the OVER() function to correctly arrange the data as shown.

2. I filtered the results to include only rows with activity_type = 'end', as we can get the timestamp for both the end and start times in a single column. The WHERE condition filters out rows which removies the activity_type = 'start'.

3. Next, I used the AVG() function, grouping the table by machine_id. The ROUND() function was applied to limit the decimals to three places.

4. Finally, I ordered the output by processing_time.

Sorry for the bad explanation, I hope you can understand :) 
# Complexity
- Time complexity: 
<!-- Add your time complexity here, e.g. $$O(n)$$ -->

- Space complexity:
<!-- Add your space complexity here, e.g. $$O(n)$$ -->

# Code
```mysql []
-- # Write your MySQL query statement below

with prev_time as 
(
    select *, lag(timestamp,1,0) over(order by machine_id, process_id, activity_type) as 'lagg' from Activity
)

select machine_id, round(avg(timestamp - lagg),3) as processing_time from prev_time 
where activity_type = 'end'
group by machine_id order by processing_time desc

```
['MySQL', 'MS SQL Server']