Self-Join with aggregation.
IntuitionEach (machine_id, process_id) has two entries:
One with activity_type = 'start'
One with activity_type = 'end'
We need to compute:process duration = en
# Intuition
Each (machine_id, process_id) has two entries:
One with activity_type = 'start'
One with activity_type = 'end'
We need to compute:

***process duration = end timestamp − start timestamp***

Self-Join the Table

Since each process has two rows (one for 'start' and one for 'end'), we need to join the table with itself.
We'll call them A1 (for 'start') and A2 (for 'end').
Join on the same machine_id and process_id, making sure:
A1.activity_type = 'start'
A2.activity_type = 'end'
Calculate Processing Time

Once joined, we can compute:
processing time = A2.timestamp − A1.timestamp

This tells us how long each process took.
Group by machine_id and Compute Average

For each machine, take the average of all processing times.
Round the result to 3 decimal places.

# Approach

**Self-join the table**

    FROM Activity a1
    JOIN Activity a2
    ON a1.machine_id = a2.machine_id 
    AND a1.process_id = a2.process_id 


**Filter for Start and End Rows**

    AND a1.activity_type = 'start' 
    AND a2.activity_type = 'end'

**Calculate Processing Time**

    a2.timestamp - a1.timestamp

**Compute Average Processing Time per Machin**
    
    ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time

**Group by Machine**

    GROUP BY a1.machine_id;

# Conclusion

 Correctly joins 'start' and 'end' rows together
 Accurately computes the process duration for each process_id
 Groups by machine_id and computes the average correctly
 Rounds to 3 decimal places as required


# Code
```mysql []
SELECT a1.machine_id, ROUND(AVG(a2.timestamp - a1.timestamp), 3) AS processing_time
FROM Activity a1
JOIN Activity a2
ON a1.machine_id = a2.machine_id 
AND a1.process_id = a2.process_id 
AND a1.activity_type = 'start' 
AND a2.activity_type = 'end'
GROUP BY a1.machine_id;

```
['MySQL']