Top Services by 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

Amazon

Problem

Every row of svc_health is one health check of a service with the uptime it recorded, and a service needs at least 5 checks before its average uptime, rounded to 2 decimals, earns a place on the board. Produce that uptime leaderboard, highest average first, keeping every service that holds one of the top three positions. Equal averages share a position, and the next average down takes the very next number with no gaps.

Uptime is a competition. Which services never blink?

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

This is a tie-tolerant top three over a filtered per-service average, dressed up as an SRE leaderboard. Anyone can write AVG(uptime) per svc_name. What separates candidates is reading top three as three positions, not three rows. LIMIT 3 or ROW_NUMBER() caps the board at three physical rows and silently drops a service tied for third. RANK() leaves a gap after a tie, so a legitimate third place never shows up. The quieter requirement is the 5-check minimum. It has to bite before positions are handed out, or thinly sampled services crowd the board.

Step 1: Reduce the checks to one row per service

GROUP BY svc_name turns 200 checks into 15 services. HAVING COUNT(*) >= 5 counts checks, not readings: a check whose uptime is NULL still happened, and AVG(uptime) skips it anyway. Round inside the CTE with ROUND(AVG(uptime), 2) so the value you sort on is the value the board shows. Two averages that differ only past the second decimal then tie on the board, which is how a reader would see them.

Step 2: Hand out positions with DENSE_RANK()

DENSE_RANK() OVER (ORDER BY avg_uptime DESC) gives equal averages the same number and the next average the very next one: 1, 1, 2, not the 1, 1, 3 that RANK() produces. The window runs over the already-filtered svc_avg, so a disqualified service never occupies a position.

Step 3: Cut on the position, not the row count

WHERE rank <= 3 in the outer query keeps every service holding a top-three position, however many rows that is. The cut lives outside the CTE that computes rank because a window result cannot be filtered in the WHERE of the same SELECT.

The solution

WITH svc_avg AS (
    SELECT
        svc_name,
        ROUND(AVG(uptime), 2) AS avg_uptime
    FROM svc_health
    GROUP BY svc_name
    HAVING COUNT(*) >= 5
),
ranked AS (
    SELECT
        svc_name,
        avg_uptime,
        DENSE_RANK() OVER (ORDER BY avg_uptime DESC) AS rank
    FROM svc_avg
)
SELECT svc_name, avg_uptime, rank
FROM ranked
WHERE rank <= 3
ORDER BY rank ASC, svc_name ASC;

SELECT svc_name, ROUND(AVG(uptime), 2) AS avg_uptime
FROM svc_health
GROUP BY svc_name
ORDER BY avg_uptime DESC
LIMIT 3;

RANK()

Equal averages share a number and the next service skips ahead. Two services tied for first are followed by 3, so the second-best average never holds position 2 and one fewer distinct average fits under rank <= 3.

`DENSE_RANK`()

Equal averages share a number and the next service takes the very next one: 1, 1, 2, 3. Each of the top three distinct averages makes the board, with all of its ties.

Common follow-up questions

• What if you needed the bottom 3 instead of the top 3? (Flip the window to `ORDER BY avg_uptime ASC`; the cut stays on positions.)
• Should a check with a NULL `uptime` count toward the 5-check minimum? (`COUNT(*)` counts it and `COUNT(uptime)` does not; `AVG(uptime)` ignores it either way.)
• Could you enforce the 5-check minimum without `HAVING`? (A `WHERE` on a count computed in an earlier CTE, or a `COUNT(*) OVER (PARTITION BY svc_name)`.)
• What if the board should only cover the last 30 days of checks? (A `WHERE` on `checked` before the `GROUP BY`, which also lets the engine prune partitions.)