Server Hot-Spot Detection
AWS
Problem Statement
AWS CloudWatch monitors millions of EC2 instances. A common task is detecting "Hot-Spots"—servers that are pinned at high utilization for long periods, which might indicate a memory leak or a runaway process.
Write a query to identify servers where the cpu_utilization was strictly greater than 90% for 10 or more consecutive 1-minute logs.
Rules:
- Logs are recorded every minute.
- Consecutive means there is no gap in the 1-minute sequence and every log in that sequence has cpu_utilization > 90.
- Output columns: server_id, hotspot_start, hotspot_end, consecutive_minutes.
- hotspot_start is the timestamp of the first log in the sequence.
- hotspot_end is the timestamp of the last log in the sequence.
- Result must be sorted by hotspot_start ascending, then server_id ascending.
Tests your understanding of
Window Functions, Gaps and Islands and Time Series
Input Tables
server_logs
| log_id(INTEGER) | server_id(INTEGER) | cpu_utilization(DECIMAL) | log_timestamp(TIMESTAMP) |
|---|---|---|---|
| 1 | 101 | 95 | 2026-03-04 10:00:00 |
| 2 | 101 | 92 | 2026-03-04 10:01:00 |
| 3 | 101 | 93 | 2026-03-04 10:02:00 |
| 4 | 101 | 94 | 2026-03-04 10:03:00 |
| 5 | 101 | 96 | 2026-03-04 10:04:00 |
| 6 | 101 | 91 | 2026-03-04 10:05:00 |
| 7 | 101 | 92 | 2026-03-04 10:06:00 |
| 8 | 101 | 93 | 2026-03-04 10:07:00 |
| 9 | 101 | 94 | 2026-03-04 10:08:00 |
| 10 | 101 | 95 | 2026-03-04 10:09:00 |
| 11 | 101 | 90 | 2026-03-04 10:10:00 |
| 12 | 102 | 95 | 2026-03-04 10:00:00 |
Expected Output
| server_id(INTEGER) | hotspot_start(TIMESTAMP) | hotspot_end(TIMESTAMP) | consecutive_minutes(INTEGER) |
|---|---|---|---|
| 101 | 2026-03-04 10:00:00 | 2026-03-04 10:09:00 | 10 |
Tags
HardWindow FunctionsGaps and IslandsTime Series
45-60 min
22%