Easy and Simple Solution | Beginner-friendly🚀
IntuitionThe goal is to calculate the average processing time for each machine across all its processes. By joining the table with itself, we can pair up the 's


# Intuition
The goal is to calculate the average processing time for each machine across all its processes. By joining the table with itself, we can pair up the 'start' and 'end' activities of each process, allowing us to calculate the time difference between them.

# Approach
1. **Self-Join:** We perform a self-join on the same table, `Activity`, to link each 'start' record (denoted as `A`) with its corresponding 'end' record (denoted as `B`). This linkage is based on matching `machine_id` and `process_id`.  
2. **Filtering Activities:** We filter `A` to only consider rows where `activity_type = 'start'` and `B` where `activity_type = 'end'`. This ensures that we’re only comparing start times with corresponding end times.
3. **Calculate Processing Time:** The difference `B.timestamp - A.timestamp` gives the processing time for each process. We then use `AVG` to find the average processing time across all processes for each machine.
4. **Grouping:** Grouping by `A.machine_id` lets us calculate the average processing time for each machine separately.
5. **Rounding:** We round the result to three decimal places for clarity.

# Complexity
- **Time complexity:**  
  - $$O(n^2)$$ — In the worst case, if every machine and process has both a 'start' and 'end', the self-join could potentially compare each row with every other row. However, due to indexing on `machine_id` and `process_id`, this can be optimized.
  
- **Space complexity:**  
  - $$O(n)$$ — Additional space mainly for storing the joined table temporarily and the result set.

# Code
```sql
SELECT A.machine_id, 
       ROUND(AVG(B.timestamp - A.timestamp), 3) AS processing_time
FROM Activity A
JOIN Activity B
ON A.machine_id = B.machine_id 
AND A.process_id = B.process_id
AND A.activity_type = 'start' 
AND B.activity_type = 'end'
GROUP BY A.machine_id;
```

---

![a9a682ef-8a56-461e-9e1b-7654b114838f_1721590703.0971212.jpeg](https://assets.leetcode.com/users/images/0f64d1be-d43a-498d-979f-0e2d9b4ee7a8_1738557101.877933.jpeg)

['MySQL']