Tiempo promedio de proceso por máquina

Hay un sitio web de fábrica que tiene varias máquinas, cada una ejecutando la misma cantidad de procesos. Escribe una solución para encontrar el tiempo promedio que tarda cada máquina en completar un proceso. El tiempo para completar un proceso representa el tiempo de finalización 'end' menos el tiempo de inicio 'start'. El tiempo promedio se calcula dividiendo el tiempo total para completar cada proceso en la máquina por la cantidad de procesos que se ejecutaron. La tabla resultante debe tener el machine_id junto con el tiempo promedio como processing_time, que debe redondearse a 3 decimales. Regresa el resultado en cualquier orden.

Tabla: Activity

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| machine_id     | int     |
| process_id     | int     |
| activity_type  | enum    |
| timestamp      | float   |
+----------------+---------+

- La tabla muestra las actividades de los usuarios de una página web de una fábrica.
- (machine_id, process_id, activity_type) es la llave primaria (combinación de columnas con valores únicos).
- machine_id es el ID de la máquina.
- process_id es el ID de un proceso ejecutándose en la máquina con machine_id.
- activity_type es un ENUM (categoría) de tipo ('start', 'end').
- timestamp es un valor flotante representando el tiempo actual en segundos.
- 'start' significa que la máquina inicia el proceso a la fecha especificada por el timestamp y 'end' significa que la máquina finaliza el proceso en el tiempo especificado por el timestamp.
- El tiempo 'start' del timestamp estará siempre antes del tiempo 'end' del timestamp para cada combinación de (machine_id, process_id).

El formato del resultado se muestra en el siguiente ejemplo.

Ejemplo 1:

Entrada:

Tabla Activity:
+------------+------------+---------------+-----------+
| machine_id | process_id | activity_type | timestamp |
+------------+------------+---------------+-----------+
| 0          | 0          | start         | 0.712     |
| 0          | 0          | end           | 1.520     |
| 0          | 1          | start         | 3.140     |
| 0          | 1          | end           | 4.120     |
| 1          | 0          | start         | 0.550     |
| 1          | 0          | end           | 1.550     |
| 1          | 1          | start         | 0.430     |
| 1          | 1          | end           | 1.420     |
| 2          | 0          | start         | 4.100     |
| 2          | 0          | end           | 4.512     |
| 2          | 1          | start         | 2.500     |
| 2          | 1          | end           | 5.000     |
+------------+------------+---------------+-----------+

Salida:

+------------+-----------------+
| machine_id | processing_time |
+------------+-----------------+
| 0          | 0.894           |
| 1          | 0.995           |
| 2          | 1.456           |
+------------+-----------------+

Explicación:
- Hay 3 máquinas cada una ejecutando 2 procesos.
- El tiempo promedio de la máquina 0 es ((1.520 - 0.712) + (4.120 - 3.140)) / 2 = 0.894
- El tiempo promedio de la máquina 1 es ((1.550 - 0.550) + (1.420 - 0.430)) / 2 = 0.995
- El tiempo promedio de la máquina 2 es ((4.512 - 4.100) + (5.000 - 2.500)) / 2 = 1.456

Solución:

WITH data AS (
     SELECT a1.machine_id machine_id, (a2.timestamp - a1.timestamp) AS t
     FROM activity a1, activity a2
     WHERE a1.machine_id = a2.machine_id
     AND a1.process_id = a2.process_id
     AND a1.activity_type = 'start'
     AND a2.activity_type='end'
) SELECT machine_id, ROUND(AVG(t), 3) AS processing_time FROM data group by machine_id;