Average Time of Process per Machine
Meta
Problem Statement
Operational efficiency at a data center is measured by how quickly machines complete assigned tasks. Each machine runs multiple processes, and each process has a start and end timestamp.
Write an SQL query to find the average time each machine takes to complete a process.
Rules:
- The time to complete a process is the end timestamp minus the start timestamp.
- The average time is calculated by taking the total time to complete all processes on the machine and dividing it by the number of processes run on that machine.
- The resulting average time should be rounded to 3 decimal places.
- Return the result table containing machine_id and processing_time.
- Results must be sorted by machine_id in ascending order.
Tests your understanding of
Basic SQL, Aggregation, Self Join and Math Functions
Input Tables
activity
| machine_id(INTEGER) | process_id(INTEGER) | activity_type(ENUM('start', 'end')) | timestamp(FLOAT) |
|---|---|---|---|
| 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 |
Expected Output
| machine_id(INTEGER) | processing_time(FLOAT) |
|---|---|
| 0 | 0.894 |
| 1 | 0.995 |
| 2 | 1.456 |
Tags
EasyBasic SQLAggregationSelf JoinMath Functions
15-20 min
62%