Services Missing Their Uptime Target — SQL Joins Problem
The status page probes every service and records 'up', 'degraded' or 'down'. A probe counts as available when it is 'up' or 'degraded'.
- Difficulty: Medium
- Topics: Joins, Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #337
Problem statement
The status page probes every service and records 'up', 'degraded' or 'down'. A probe counts as available when it is 'up' or 'degraded'. Each service's tier sets its uptime target: 99% for 'gold', 95% for 'silver', 90% for 'bronze'. A service misses its target when its share of available probes is below the target; exactly on target is a pass. Services with no probes are not reported.
Return the services that missed, with the columns service_name, tier, checks and uptime_pct (100 * available / checks, rounded to 2 decimals), ordered by uptime_pct ascending, then service_name.
Tables
Table: Service
| Column | Type |
|---|---|
| service_id | int |
| service_name | varchar |
| tier | enum(gold, silver, bronze) |
Primary key: service_id.
One row per service on the status page. Names are unique.
Table: HealthCheck
| Column | Type |
|---|---|
| check_id | int |
| service_id | int |
| checked_at | datetime |
| status | enum(up, degraded, down) |
Primary key: check_id.
One row per probe result.
Examples
Example 1
Service
| service_id | service_name | tier |
|---|---|---|
| 1 | checkout-api | gold |
| 2 | search-svc | silver |
| 3 | notify-worker | bronze |
| 4 | catalog-api | silver |
| 5 | auth-svc | gold |
HealthCheck
| check_id | service_id | checked_at | status |
|---|---|---|---|
| 1 | 1 | 2025-11-01 00:00:00 | up |
| 2 | 1 | 2025-11-01 00:05:00 | degraded |
| 3 | 1 | 2025-11-01 00:10:00 | down |
| 4 | 2 | 2025-11-01 00:00:00 | up |
| 5 | 2 | 2025-11-01 00:05:00 | up |
| 6 | 3 | 2025-11-01 00:00:00 | down |
| 7 | 3 | 2025-11-01 00:05:00 | up |
| 8 | 4 | 2025-11-01 00:00:00 | down |
| 9 | 4 | 2025-11-01 00:05:00 | up |
Output
| service_name | tier | checks | uptime_pct |
|---|---|---|---|
| catalog-api | silver | 2 | 50 |
| notify-worker | bronze | 2 | 50 |
| checkout-api | gold | 3 | 66.67 |
How to solve Services Missing Their Uptime Target
Group the probes by service and count two things: all probes, and the available ones — a SUM(CASE WHEN status IN ('up', 'degraded') THEN 1 ELSE 0 END). The inner JOIN to the probes means a service with no probes produces no group, which is what the statement asks for.
The target is a property of the tier, so it can be written inline as CASE s.tier WHEN 'gold' THEN 99 … (the tier is a grouped column, so HAVING may use it) or kept as data in a small derived table joined on the tier, as the alternative does. The comparison is done by cross-multiplying — 100 * available < target * checks — rather than comparing a computed percentage: 19 available probes out of 20 is exactly 95%, and the integer form leaves that silver service passing with no chance of a rounding error turning it into a miss. The displayed uptime_pct is rounded to 2 decimals.
The order is by the rounded percentage and then by name, which is unique, so it is fully determined. One scan of the probes and a join to the small service table.
Reference solution (MySQL)
SELECT s.service_name, s.tier, COUNT(*) AS checks,
ROUND(100 * SUM(CASE WHEN h.status IN ('up', 'degraded') THEN 1 ELSE 0 END) / COUNT(*), 2) AS uptime_pct
FROM Service s
JOIN HealthCheck h ON h.service_id = s.service_id
GROUP BY s.service_id, s.service_name, s.tier
HAVING 100 * SUM(CASE WHEN h.status IN ('up', 'degraded') THEN 1 ELSE 0 END)
< COUNT(*) * CASE s.tier WHEN 'gold' THEN 99 WHEN 'silver' THEN 95 ELSE 90 END
ORDER BY uptime_pct, s.service_nameAnother way
WITH avail AS (
SELECT service_id, COUNT(*) AS checks, SUM(IF(status <> 'down', 1, 0)) AS available
FROM HealthCheck GROUP BY service_id
)
SELECT s.service_name, s.tier, a.checks, ROUND(100 * a.available / a.checks, 2) AS uptime_pct
FROM avail a
JOIN Service s ON s.service_id = a.service_id
JOIN (SELECT 'gold' AS tier, 99 AS target UNION ALL SELECT 'silver', 95 UNION ALL SELECT 'bronze', 90) t ON t.tier = s.tier
WHERE 100 * a.available < t.target * a.checks
ORDER BY uptime_pct, s.service_name← Customers Contacting Support Again Within a Week · Error Counts by Region and Environment From Hostnames →