Hosts That Hit CPU Saturation — SQL Aggregation Problem
The monitoring agent samples every host's CPU usage every five minutes. A sample with cpu_pct NULL is one the agent failed to collect.
- Difficulty: Easy
- Topics: Aggregation
- Dialect: MySQL
- Problem: #325
Problem statement
The monitoring agent samples every host's CPU usage every five minutes. A sample with cpu_pct NULL is one the agent failed to collect. The capacity review flags a host as saturated when its highest collected sample is at least 90.0 percent.
Return the saturated hosts with the columns host, peak_cpu (the highest cpu_pct) and samples (how many non-NULL samples the host has), ordered by peak_cpu descending, then host ascending.
Tables
Table: CpuSample
| Column | Type |
|---|---|
| host | varchar |
| sampled_at | datetime |
| cpu_pct | decimal |
Primary key: host, sampled_at.
One row per host per five-minute sample. cpu_pct is 0–100 with one decimal, or NULL when the sample was missed.
Examples
Example 1
CpuSample
| host | sampled_at | cpu_pct |
|---|---|---|
| web-01 | 2025-04-02 10:00:00 | 72.5 |
| web-01 | 2025-04-02 10:05:00 | 93.1 |
| db-01 | 2025-04-02 10:00:00 | 90 |
| db-01 | 2025-04-02 10:05:00 | NULL |
| db-01 | 2025-04-02 10:10:00 | 61.4 |
| cache-01 | 2025-04-02 10:00:00 | 89.9 |
| api-02 | 2025-04-02 10:00:00 | 93.1 |
| api-02 | 2025-04-02 10:05:00 | 40.2 |
| api-02 | 2025-04-02 10:10:00 | 55 |
Output
| host | peak_cpu | samples |
|---|---|---|
| api-02 | 93.1 | 3 |
| web-01 | 93.1 | 2 |
| db-01 | 90 | 2 |
How to solve Hosts That Hit CPU Saturation
Each output row summarises one host, so the samples are grouped by host. MAX(cpu_pct) is the host's peak — aggregate functions ignore NULLs, so a missed sample never becomes the peak — and COUNT(cpu_pct) counts only the samples that were actually collected, which is what samples means; COUNT(*) would also count the missed ones.
The condition "peak at least 90" is about a value that exists only after grouping, so it goes in HAVING MAX(cpu_pct) >= 90.0. A WHERE clause would instead drop the cool samples before grouping, which leaves the right hosts but the wrong samples count. The comparison is inclusive: a host peaking at exactly 90.0 is saturated, one at 89.9 is not.
Two hosts can peak at the same value, so the order needs host as the second key. The alternative finds the saturated hosts with an IN subquery and aggregates only those — the same answer, two passes instead of one.
Reference solution (MySQL)
SELECT host, MAX(cpu_pct) AS peak_cpu, COUNT(cpu_pct) AS samples
FROM CpuSample
GROUP BY host
HAVING MAX(cpu_pct) >= 90.0
ORDER BY peak_cpu DESC, hostAnother way
SELECT host, MAX(cpu_pct) AS peak_cpu, COUNT(cpu_pct) AS samples
FROM CpuSample
WHERE host IN (SELECT host FROM CpuSample WHERE cpu_pct >= 90.0)
GROUP BY host
ORDER BY peak_cpu DESC, host← Open Ticket Load per Support Agent · Deployment Outcomes per Service →