+----------------+---------+
|Column Name |Type|+----------------+---------+
| machine_id | int || process_id | int || activity_type | enum ||timestamp| float |+----------------+---------+
The table shows the user activities for a factory website.
(machine_id, process_id, activity_type) is the primarykey (combination of columns withuniquevalues) of this table.
machine_id is the ID of a machine.
process_id is the ID of a process running on the machine with ID machine_id.
activity_type is an ENUM (category) oftype ('start', 'end').
timestampis a float representing the current time in seconds.
'start' means the machine starts the process at the given timestampand'end' means the machine ends the process at the given timestamp.
The 'start'timestamp will always be before the 'end'timestampforevery (machine_id, process_id) pair.
It is guaranteed that each (machine_id, process_id) pair has a 'start'and'end'timestamp.
There is a factory website that has several machines each running the same number of processes. Write a solution to find the average time each machine takes to complete a process.
The time to complete a process is the 'end' timestamp minus the 'start' timestamp. The average time is calculated by the total time to complete every process on the machine divided by the number of processes that were run.
The resulting table should have the machine_id along with the average time as processing_time, which should be rounded to 3 decimal places.
We need to pair each process’s start and end times, compute the duration for each process, and then average these durations for each machine. This can be done by self-joining the table on process and machine, filtering for start and end, and then aggregating.
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'GROUPBY a.machine_id;