10 Lowest Uptime Services

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

Google

Problem

The SRE team is preparing a reliability review for the quarterly infrastructure meeting. Each service has multiple health check records; surface the 10 least reliable, judged by their lowest recorded uptime, and if several services are tied for that 10th spot include all of them. List each service with its lowest uptime, weakest first.

Ten services at the bottom of the reliability chart.

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

Underneath the reliability review, this is a per-service floor followed by a cutoff drawn on values, not rows. Everyone finds MIN(uptime). What separates candidates is the order of operations and where the line falls. Sort the raw svc_health rows and take ten, and you are listing checks: 'payment-api' and 'search-api' each show up twice, and half the worst services never appear. Collapse to services correctly but cut with LIMIT 10, and a service tied at the tenth-worst uptime is kept or dropped by whatever order the engine happened to emit. The review then silently misses a failing service.

Build it in three moves

Step 1: Collapse the checks to one floor per service

GROUP BY svc_name with MIN(uptime) turns every check into one number per service: its worst reading. This has to come first because the cutoff is about services. MIN also skips the 15 checks that never recorded an uptime, which is exactly right: a missing reading says nothing about how low a service dipped.

Step 2: Number the floors with DENSE_RANK()

DENSE_RANK() OVER (ORDER BY MIN(uptime) ASC) gives equal floors the same number, so the filter keeps every service sitting on the tenth value. It can share a SELECT with the GROUP BY because windows run after grouping: the window sees one row per service.

Step 3: Cut at 10 and list weakest first

Filter rnk <= 10 in the outer query, because a window result cannot be filtered in the WHERE of the query that computes it. Then sort by min_uptime ascending. On this data the cut lands cleanly: 'gateway' is tenth at 91.65, and five newer services share 95.0 just past it. Move the cutoff to 11 and all five arrive together, which is the case the rule exists for.

The solution

SELECT svc_name, min_uptime
FROM (
    SELECT
        svc_name,
        MIN(uptime) AS min_uptime,
        DENSE_RANK() OVER (ORDER BY MIN(uptime) ASC) AS rnk
    FROM svc_health
    GROUP BY svc_name
) ranked
WHERE rnk <= 10
ORDER BY min_uptime ASC

SELECT svc_name, uptime
FROM svc_health
ORDER BY uptime
LIMIT 10

`LIMIT 10` on the floors

Stops at exactly ten rows. If three services share the tenth-worst floor, two of them vanish, and which two depends on the engine's sort, so reruns can disagree.

`rnk <= 10` on `DENSE_RANK()`

Stops at the tenth floor value. Every service sharing it comes along, and the answer is the same on every run.

Common follow-up questions

• What if the review used each service's average uptime instead of its lowest? (Swapping the aggregate leaves the cutoff logic untouched.)
• The meeting insists on exactly ten rows: how do you break ties at the cutoff by `svc_name`? (Turns the tie rule into a tiebreak: `ROW_NUMBER()` ordered by the floor, then the name.)
• What changes if you need the ten worst services per `region`? (Adds `PARTITION BY region` to the window and moves `region` into the grouping.)
• Could you solve this without a window function? (A correlated count of services with a lower floor works and shows you understand what the rank means.)