Average Time of Process per Machine

Average Time of Process per Machine

What is this problem about?

The Average Time of Process per Machine interview question is a SQL problem that requires you to calculate the average time it takes for a machine to complete a process. You are given an Activity table with columns machine_id, process_id, activity_type ('start' or 'end'), and timestamp. Each process has a start and an end. The goal is to find the average (end_timestamp - start_timestamp) for all processes on each machine. This Average Time of Process per Machine coding problem is a test of row-to-row comparison in databases.

Why is this asked in interviews?

Companies like J.P. Morgan, Uber, and Microsoft use this to evaluate a candidate's ability to perform self-joins or use aggregate functions with conditional logic. It tests your understanding of how to pair related events (start and end) that are stored as separate rows in a log-style table.

Algorithmic pattern used

This follows the Database interview pattern of "Self-Join and Average." You can join the Activity table with itself where machine_id and process_id match, but one row is the 'start' and the other is the 'end'. Alternatively, you can use a SUM(CASE WHEN activity_type = 'end' THEN timestamp ELSE -timestamp END) approach grouped by machine.

Example explanation

Machine 1, Process A: Start at 0.5, End at 1.5. (Time = 1.0) Machine 1, Process B: Start at 2.0, End at 4.5. (Time = 2.5)

• Total Time for Machine 1: 1.0 + 2.5 = 3.5.
• Number of Processes: 2.
• Average Time: 3.5 / 2 = 1.75.

Common mistakes candidates make

• Incorrect Joining: Not matching on both machine_id and process_id, which leads to pairing starts with the wrong ends.
• Ignoring Process Count: Dividing the total time by the total number of rows (starts + ends) instead of the number of unique processes.
• Rounding Errors: Forgetting to round the final result to the specified number of decimal places (usually 3).

Interview preparation tip

Practice "Sessionization" logic in SQL. Converting event logs (start/end rows) into duration metrics is a fundamental skill for backend and data engineering roles.