Average Time of Process

Each process leaves two rows in one table — a start and an end sharing the same (machine_id, process_id) — so to compute a duration you must bring those two rows onto the same row, which is exactly what a self-join does: join Activity to itself, forcing the left copy to be the start and the right copy the matching end, then average end.timestamp − start.timestamp per machine.

Problem

Table Activity(machine_id, process_id, activity_type, timestamp), where activity_type is the enum 'start' | 'end' and timestamp is a float of seconds. The primary key is (machine_id, process_id, activity_type), so every process has exactly one start row and one end row, and the start always precedes the end. Every machine runs the same number of processes. Return each machine_id with its average process duration as processing_time, rounded to 3 decimal places, in any order.

The query

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;

The two type predicates are the load-bearing part. a1 is pinned to the start row and a2 to the matching end row of the same process, so each surviving joined row carries both timestamps and the subtraction a2.timestamp − a1.timestamp is one process's duration. AVG then averages those durations within each machine_id group, and ROUND(…, 3) truncates the result to three decimals.

Worked example

Take the canonical LeetCode input — three machines, two processes each:

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   |
+------------+------------+---------------+-----------+

Step 1 — the joined rows (one per process)

This is the row the original page hid behind … placeholders. After the join, each process collapses to a single row carrying both timestamps and the duration:

+------------+------------+----------+--------+----------+
| machine_id | process_id | a1 start | a2 end | duration |
+------------+------------+----------+--------+----------+
|     0      |     0      |  0.712   | 1.520  |  0.808   |
|     0      |     1      |  3.140   | 4.120  |  0.980   |
|     1      |     0      |  0.550   | 1.550  |  1.000   |
|     1      |     1      |  0.430   | 1.420  |  0.990   |
|     2      |     0      |  4.100   | 4.512  |  0.412   |
|     2      |     1      |  2.500   | 5.000  |  2.500   |
+------------+------------+----------+--------+----------+

Step 2 — group, average, round

GROUP BY a1.machine_id partitions those six rows by machine; AVG averages each partition's duration; ROUND(…, 3) finishes:

m0: (0.808 + 0.980) / 2 = 1.788 / 2 = 0.894  -> 0.894
m1: (1.000 + 0.990) / 2 = 1.990 / 2 = 0.995  -> 0.995
m2: (0.412 + 2.500) / 2 = 2.912 / 2 = 1.456  -> 1.456

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

The averages here land exactly on three decimals, so ROUND changes nothing — but it must stay in the query, because real data won't be so tidy.

Why the naive version is wrong

A common first attempt skips the self-join and tries to subtract within a single scan:

-- WRONG: there is no single row that has both timestamps
SELECT machine_id, AVG(end_ts - start_ts) ...   -- no such columns exist

The data is in long form — two rows per process — so the subtraction has no two columns to operate on until you pivot the pair onto one row. The self-join is that pivot. An alternative correct form makes the sign explicit and needs no join:

SELECT machine_id,
       ROUND(AVG(CASE WHEN activity_type = 'end'   THEN  timestamp
                      WHEN activity_type = 'start' THEN -timestamp END) * 2, 3) AS processing_time
FROM   Activity
GROUP BY machine_id;

This sums (+end) + (−start) across the group. The average of all 2N signed values is (Σend − Σstart) / 2N; multiplying by 2 gives (Σend − Σstart) / N, the same per-process average. It only works because each machine has the same count of starts and ends.

Pitfalls

• Dropping a type predicate. If you only constrain a1.activity_type = 'start' and forget a2.activity_type = 'end', a2 matches both rows of the process — including the start joining to itself (duration 0) — silently halving and corrupting the average. Both predicates are mandatory.
• Pinning the type on the wrong alias. Swap them and you compute start − end, a negative duration. The AVG still runs and returns a plausible-looking negative number, so the bug passes a smoke test and fails the grader.
• Forgetting ROUND. Float averages like 0.8939999… print with a long tail; the problem demands exactly 3 decimals. Omitting ROUND is the most common silent failure on this problem.
• Float equality on the join. Join on the integer keys (machine_id, process_id), never on timestamp. Equality comparisons against floats are unreliable and would mis-pair rows.
• Selecting a2.machine_id while grouping by a1.machine_id. They're equal under this join, but mixing aliases between SELECT and GROUP BY trips strict ONLY_FULL_GROUP_BY modes (MySQL 8, Postgres). Keep them consistent.

Takeaways

• Two rows that describe one event (start/end, open/close, in/out) become one row via a self-join keyed on the shared identifier, with type predicates pinning each alias to its role.
• Put the 'start'/'end' filters in the ON clause — they define which pairs are legal, and both are required or the join over-matches.
• AVG(end − start) already divides by the process count; you don't add a manual COUNT. Just wrap it in ROUND(…, 3) to meet the output contract.
• The signed-CASE single-pass form is a faster, join-free alternative when starts and ends are balanced per group — worth knowing for large tables.

Based on LeetCode 1661 "Average Time of Process per Machine" and its official example data, which yields exactly 0.894 / 0.995 / 1.456. SQL standard semantics for self-joins, AVG, and GROUP BY per the PostgreSQL and MySQL 8 reference manuals (including ONLY_FULL_GROUP_BY behavior). Re-authored and deepened for this guide: the original trace showed only … placeholders for the joined rows and listed unrounded intermediates (0.89478, 0.99538, 1.45656) that did not round to the stated finals; replaced with the real per-process durations and arithmetic that round correctly, plus the join diagram, the signed-CASE alternative, and the pitfalls section.