Services Missing Their Uptime Target

Medium
337.

Services Missing Their Uptime Target

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

ColumnTypeValues
service_idint
service_namevarchar
tierenumgold, silver, bronze

Primary key: service_id.

One row per service on the status page. Names are unique.

Table: HealthCheck

ColumnTypeValues
check_idint
service_idint
checked_atdatetime
statusenumup, degraded, down

Primary key: check_id.

One row per probe result.

Example

The rows must come back in the order shown.

Service

service_idservice_nametier
1checkout-apigold
2search-svcsilver
3notify-workerbronze
4catalog-apisilver
5auth-svcgold

HealthCheck

check_idservice_idchecked_atstatus
112025-11-01 00:00:00up
212025-11-01 00:05:00degraded
312025-11-01 00:10:00down
422025-11-01 00:00:00up
522025-11-01 00:05:00up
632025-11-01 00:00:00down
732025-11-01 00:05:00up
842025-11-01 00:00:00down
942025-11-01 00:05:00up

Output

service_nametierchecksuptime_pct
catalog-apisilver250
notify-workerbronze250
checkout-apigold366.67
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.

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

ColumnType
service_idint
service_namevarchar
tierenum(gold, silver, bronze)

Primary key: service_id.

One row per service on the status page. Names are unique.

Table: HealthCheck

ColumnType
check_idint
service_idint
checked_atdatetime
statusenum(up, degraded, down)

Primary key: check_id.

One row per probe result.

Examples

Example 1

Service

service_idservice_nametier
1checkout-apigold
2search-svcsilver
3notify-workerbronze
4catalog-apisilver
5auth-svcgold

HealthCheck

check_idservice_idchecked_atstatus
112025-11-01 00:00:00up
212025-11-01 00:05:00degraded
312025-11-01 00:10:00down
422025-11-01 00:00:00up
522025-11-01 00:05:00up
632025-11-01 00:00:00down
732025-11-01 00:05:00up
842025-11-01 00:00:00down
942025-11-01 00:05:00up

Output

service_nametierchecksuptime_pct
catalog-apisilver250
notify-workerbronze250
checkout-apigold366.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_name

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