Server Utilization
ASKED IN INTERVIEWAWS
Problem Statement
Calculate the total uptime in full hours for all servers. Uptime is the duration between a START event and the subsequent STOP event.
Rules:
- Output columns: total_uptime_hours.
- Only pair a START with the very next STOP for that specific server.
- Convert the total duration into hours (discarding fractional minutes/seconds).
- Return a single integer.
Tests your understanding of
Window Functions, LEAD, Timestamp Arithmetic and Aggregation
Input Tables
server_logs
| server_id(INTEGER) | status(VARCHAR) | event_time(TIMESTAMP) |
|---|---|---|
| 1 | start | 2024-01-01 10:00:00 |
| 1 | stop | 2024-01-01 12:30:00 |
| 2 | start | 2024-01-01 13:00:00 |
| 2 | stop | 2024-01-01 15:00:00 |
server_specs
| server_id(INTEGER) | instance_type(VARCHAR) |
|---|---|
| 1 | m5.large |
region_data
| server_id(INTEGER) | region(VARCHAR) |
|---|---|
| 1 | us-east-1 |
Expected Output
| total_uptime_hours(INTEGER) |
|---|
| 4 |
Tags
HardASKED IN INTERVIEWWindow FunctionsLEADTimestamp ArithmeticAggregation
30-40 min
34%