Average Time of Process per Machine

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

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.

Database Schema

Activity

Column Name	Example Value
machine_id	0
process_id	0
activity_type	start
timestamp	0.712

Example

Activity

machine_id	process_id	activity_type	timestamp
0	0	start	0.712
0	0	end	1.52
0	1	start	3.14
0	1	end	4.12
1	0	start	0.55
1	0	end	1.55
1	1	start	0.43
1	1	end	1.42
2	0	start	4.1
2	0	end	4.512
2	1	start	2.5
2	1	end	5

Output

machine_id	processing_time
0	0.894
1	0.995
2	1.456

Explanation:

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.