1661. Average Time of Process per Machine

SQL 50 · Basic Joins (day 2). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.

EasySolve it on LeetCode ↗

Your work

Status Notes Your solution (MySQLPostgreSQL)

Hints

Open one at a time. Try to finish with as few as you can.

1. What does one output row represent?

One machine.

2. Approach

Self join start rows to end rows on machine_id and process_id, AVG(end - start), ROUND to 3.

3. The trap

Alternative: conditional aggregation, AVG of end timestamps minus AVG of start timestamps.timestamp is a float column here, and ROUND(double, 3) does not exist in PostgreSQL. Cast to numeric first.

Reference solution

Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.

Show the MySQLPostgreSQL solution

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;SELECT a.machine_id,
  ROUND(AVG(b.timestamp - a.timestamp)::numeric, 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;

Patterns used

Which join · MySQL vs PostgreSQL