Services at Median Uptime

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

General Assembly

Problem

Which services land in the two weakest of four equal tiers when the fleet is split by each service's average uptime across all of its health checks in svc_health? For each one, the SRE team's triage sheet needs that average, how far it sits below the fleet's most reliable service, and how far below the strongest service in its own tier.

Exactly at the median. Not above, not below.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this really is

Beneath the on-call story this is a population-relative bucketing problem with two reference comparisons bolted on. First you collapse a firehose of health checks into one uptime number per service, then you cut the services into four population-sized tiers and keep the weakest half. Then each survivor gets two yardsticks: how far it trails the single best service in the whole fleet, and how far it trails the best service in its own tier. Almost everyone reaches for the tiering idea. Where they fall is grain: bucket the raw check rows and a service polled every minute floods the low tiers while a quiet one vanishes, so you end up tiering monitoring volume instead of reliability.

Build it in four moves

Step 1: Collapse to one uptime per service

The table is a stream of individual checks, but reliability is a property of the service. Average uptime per svc_name first, so every service is exactly one member of the population. Skip this and you tier raw events, and the noisiest service wins.

Step 2: Cut the services into four tiers, keep the weakest two

NTILE(4) OVER (ORDER BY avg_uptime) hands each service a tier from 1 (lowest uptime) to 4. Keeping tiers 1 and 2 with WHERE a.reliability_tier <= 2 is the below-median half, and because NTILE works off the ordered set it never needs a hardcoded count.

Step 3: Self-join on the tier to find each tier's leader

The tier leader is a per-group reference a single global MAX cannot give you. Join tiered to itself on equal reliability_tier, so each service sees only its own tier's members, then MAX(b.avg_uptime) is that tier's ceiling. Subtract the service's own average and you have gap_to_tier_best.

Step 4: Measure the gap to the fleet best

The fleet leader is global, so the scalar subquery (SELECT MAX(avg_uptime) FROM tiered) pins it once, and each survivor's shortfall from it is gap_to_best. It reads over all of tiered, not just the weak half, which is exactly why it cannot share the self-join's scope.

The solution

WITH svc_uptime AS (
    SELECT svc_name, AVG(uptime) AS avg_uptime
    FROM svc_health
    GROUP BY svc_name
),
tiered AS (
    SELECT svc_name, avg_uptime,
           NTILE(4) OVER (ORDER BY avg_uptime) AS reliability_tier
    FROM svc_uptime
)
SELECT a.svc_name,
       a.avg_uptime,
       (SELECT MAX(avg_uptime) FROM tiered) - a.avg_uptime AS gap_to_best,
       MAX(b.avg_uptime) - a.avg_uptime AS gap_to_tier_best
FROM tiered a
JOIN tiered b ON b.reliability_tier = a.reliability_tier
WHERE a.reliability_tier <= 2
GROUP BY a.svc_name, a.avg_uptime

Bucketing raw check rows (wrong grain)

NTILE over the raw checks tiers individual readings. A service polled every minute contributes thousands of rows and drowns the low tiers; a rarely checked service barely registers. The output is a list of checks, not services.

Bucketing services (correct grain)

GROUP BY svc_name first, so each service is one member of the population. NTILE then tiers the services evenly and the weakest half is a genuine set of low-uptime services.

Common follow-up questions

• The team now wants the weakest decile instead of the weakest half. What changes? (Tests whether `NTILE(n)` generalizes and the tier filter adjusts without restructuring.)
• Some `uptime` readings are `NULL`. How does that move a service's average and its tier? (Probes `NULL` handling in `AVG` over `svc_health.uptime` and whether a mostly-`NULL` service should tier at all.)
• How would you weight recent checks more heavily than old ones when computing each service's uptime? (Tests grain and weighting: a raw average versus a recency-aware measure of reliability built from `checked`.)