Hosts That Hit CPU Saturation

Easy
325.

Hosts That Hit CPU Saturation

EasyAggregation

MySQL

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

ColumnType
hostvarchar
sampled_atdatetime
cpu_pctdecimal

Primary key (together): 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.

Example

The rows must come back in the order shown.

CpuSample

hostsampled_atcpu_pct
web-012025-04-02 10:00:0072.5
web-012025-04-02 10:05:0093.1
db-012025-04-02 10:00:0090
db-012025-04-02 10:05:00NULL
db-012025-04-02 10:10:0061.4
cache-012025-04-02 10:00:0089.9
api-022025-04-02 10:00:0093.1
api-022025-04-02 10:05:0040.2
api-022025-04-02 10:10:0055

Output

hostpeak_cpusamples
api-0293.13
web-0193.12
db-01902
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
hostvarchar
sampled_atdatetime
cpu_pctdecimal

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

hostsampled_atcpu_pct
web-012025-04-02 10:00:0072.5
web-012025-04-02 10:05:0093.1
db-012025-04-02 10:00:0090
db-012025-04-02 10:05:00NULL
db-012025-04-02 10:10:0061.4
cache-012025-04-02 10:00:0089.9
api-022025-04-02 10:00:0093.1
api-022025-04-02 10:05:0040.2
api-022025-04-02 10:10:0055

Output

hostpeak_cpusamples
api-0293.13
web-0193.12
db-01902

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, host

Another 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 →