Multiple effective approach, all using subquery (without JOIN)
IntuitionFirst extract out the table with its duration for each process_id
Then from that table, find the average of each machine_idApproach

Partition by/Group
# Intuition
First extract out the table with its duration for each `process_id`
Then from that table, find the average of each `machine_id`

# Approach
1. **Partition by/Group by** `machine_id` and `process_id` (this makes sure we are are working for each `process_id` of each `machine_id`) and on that partition find the difference of *end* `timestamp` and *start* `timestamp`
    > We have multiple options/methods to solve this particular sub problem, following are some: 
    1. use `MAX()` to extract out a numeric value of `timestamp` (eg. `MAX('1.520', NULL)` is `1.520`) then, substract the numberic values of `timestamp`  
    
        ```sql
        SELECT 
        machine_id,
            MAX(CASE WHEN activity_type = 'end' THEN timestamp ELSE NULL END) - 
            MAX(CASE WHEN activity_type = 'start' THEN timestamp ELSE NULL END) AS duration
        FROM Activity
        GROUP BY machine_id, process_id
        ```
    2.  use `sum('end', '-start')` approach to calculate the difference
        - `1 * timestamp` if `activity_type = 'end'`
        - `-1 * timestamp` if `activity_type = 'start'`;
        
            ```sql
            select 
            machine_id,
                sum(if(activity_type = 'end', 1, -1) * timestamp) as duration
            from Activity
            group by machine_id, process_id
            ```
    3. **smarter way** (`Optimal solution`): use `min()` as 'start' and `max()` as 'end' and substract the value to get the difference
        ```sql
        select 
            machine_id,
            process_id,
            max(timestamp) - min(timestamp) as duration
        from activity
        group by machine_id, process_id
        ```
    **Output** (from either of the options above):
    |machine_id|	process_id|	duration|
    |---|---| ---|
    |0	|0	|0.808|
    |0	|1	|0.980|
    |1	|0	|1.000|
    |1	|1	|0.990|
    |2	|0	|0.412|
    |2	|1	|2.500|
    |...|...|...

2. now the job gets simple, we just have to find the average time for each `machine_id` (so, this time **partition by/group by** `machine_id`)
    - just find the average using in-built `AVG()` function
    - and then use `ROUND(<value>, 3)` to round the value to `3` decimal points
# Full Codes
- **Method a**-
    ```mysql []
    SELECT 
        machine_id, 
        ROUND(AVG(duration),3) AS processing_time
    FROM (
    -- Subquery to calculate duration for each process_id
        SELECT 
            machine_id,
            MAX(CASE WHEN activity_type = 'end' THEN timestamp ELSE NULL END) - 
            MAX(CASE WHEN activity_type = 'start' THEN timestamp ELSE NULL END) AS duration
        FROM Activity
        GROUP BY machine_id, process_id
    ) AS subquery
    GROUP BY machine_id;
    ```
- **Method b**-
    ```mysql []
    select machine_id, round(avg(duration),3) as processing_time from (
        select 
            machine_id,
            sum(if(activity_type = 'end', 1, -1) * timestamp) as duration
        from Activity
        group by machine_id, process_id
    ) as subquery
    group by machine_id
    ```
- **Method c** `(optimal sol)`-
    ```mysql []
    select machine_id, round(avg(duration), 3) as processing_time
    from (
        select 
            machine_id,
            process_id,
            max(timestamp) - min(timestamp) as duration
        from activity
        group by machine_id, process_id
    ) as subquery
    group by machine_id
    ```


['MySQL']