Average Time of Process per Machine
Preview mode. Log in to edit, run, submit, and save progress.
Average Time of Process per Machine
You are given an Activity table that logs the start and end timestamps of processes running on machines. Each machine runs one or more processes. Every process has exactly two rows in the table - one with activity_type = 'start' and one with activity_type = 'end'. The processing time of a single process is (end timestamp - start timestamp). Write a SQL query to find the average processing time per machine, rounded to 3 decimal places. processing_time = AVG(end_timestamp - start_timestamp) per machine Return the result ordered by machine_id.
Examples
Machine 0: ((1.520-0.712) + (4.120-3.140)) / 2 = (0.808 + 0.980) / 2 = 0.894. Machine 1: ((1.550-0.550) + (1.420-0.430)) / 2 = (1.000 + 0.990) / 2 = 0.995. Machine 2: ((4.512-4.100) + (5.000-2.500)) / 2 = (0.412 + 2.500) / 2 = 1.456.
Approach hint
Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.
Common mistake
Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.