# 10 Lowest Uptime Services

> Ten services at the bottom of the reliability chart.

Canonical URL: <https://datadriven.io/problems/10_lowest_uptime_services>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The SRE team is preparing a reliability review for the quarterly infrastructure meeting. Each service has multiple health check records, and the team needs to surface the 10 worst-performing services based on their lowest recorded uptime. If multiple services are tied at the 10th position, include all of them. Return the service name and its lowest uptime value.

## Worked solution and explanation

### Why this problem exists in real interviews

This probes two distinct skills: whether you understand that **aggregation must precede ranking** (a common source of incorrect results), and whether you know the semantic differences between `ROW_NUMBER`, `RANK`, and `DENSE_RANK`. These are foundational window function concepts that appear in nearly every SQL round at L5+.

> **Trick to Solving**
>
> The phrase "if multiple are tied, include all of them" is the signal. Whenever a top-N question requires tie inclusion, the answer is `DENSE_RANK`. Spot it by looking for language about ties, "at least N", or "include all at position N".
> 
> 1. Recognize the tie-inclusion language in the prompt
> 2. Use `DENSE_RANK()` instead of `ROW_NUMBER()` or `LIMIT`
> 3. Aggregate to the correct grain before ranking

---

### Break down the requirements

#### Step 1: Aggregate to one row per service

`GROUP BY svc_name` with `MIN(uptime)` collapses the 63M health check rows into 200 rows (one per service). This is the correct grain for ranking.

#### Step 2: Rank with DENSE_RANK

`DENSE_RANK() OVER (ORDER BY MIN(uptime) ASC)` assigns the same rank to tied values and never skips numbers, so filtering to rank ≤ 10 includes all ties at position 10.

#### Step 3: Filter and order

Wrap in a subquery, filter `WHERE rnk ≤ 10`, and `ORDER BY min_uptime ASC` to surface the worst services first.

---

### The solution

**Optimal approach: aggregate then rank**

```sql
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
```

> **Cost Analysis**
>
> The `GROUP BY` reduces 63M rows to 200 before the window function runs. The ranking step is trivially cheap. The bottleneck is the full-table scan for the aggregate. A partial index on `(svc_name, uptime)` or a materialized view would help at production scale.

> **Interviewers Watch For**
>
> The most common failure is using `LIMIT 10`, which silently drops tied rows and produces non-deterministic output. Strong candidates immediately flag "include ties" as a `DENSE_RANK` signal without being prompted.

> **Common Pitfall**
>
> Ranking before aggregating inverts the logic: you would rank individual health checks instead of services. Always aggregate to the output grain before applying window functions.

---

## Common follow-up questions

- What if you needed the bottom 10 by average uptime instead of minimum? _(Tests whether you can swap the aggregate without changing the ranking logic.)_
- How would the query change if the table had 50,000 distinct services instead of 200? _(At scale, the subquery output grows and the window sort becomes non-trivial.)_
- What if you needed to break ties by service name alphabetically? _(Tests compound ORDER BY inside DENSE_RANK: ORDER BY MIN(uptime) ASC, svc_name ASC.)_
- Could you solve this without a window function? _(A self-join or correlated subquery approach is valid but less readable and often slower.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/10_lowest_uptime_services)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.