# Top Services by Uptime

> Uptime is a competition. Which services never blink?

Canonical URL: <https://datadriven.io/problems/top_services_by_uptime>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The SRE team maintains an uptime leaderboard. Rank services by average uptime, but only include services with at least 5 health checks. Tied services share the same rank with no gaps. Return the top 3 ranks, including ties. Round to 2 decimal places.

## Worked solution and explanation

### Why this problem exists in real interviews

This combines three concepts in one query: conditional aggregation with `HAVING`, `DENSE_RANK` for tie-inclusive ranking, and rounding. Interviewers use this to test whether candidates can layer multiple SQL features without losing track of evaluation order.

> **Trick to Solving**
>
> The phrase "tied services share the same rank with no gaps" is the `DENSE_RANK` signal. Whenever you see tie-inclusive top-N language, avoid `ROW_NUMBER` or `LIMIT`.
> 
> 1. Spot the "no gaps" or "include ties" phrasing
> 2. Aggregate to the correct grain first (one row per service)
> 3. Apply `DENSE_RANK()` over the aggregated metric
> 4. Filter in an outer query to the desired rank cutoff

---

### Break down the requirements

#### Step 1: Aggregate per service with a minimum check count

`GROUP BY svc_name` with `HAVING COUNT(*) >= 5` ensures only services with sufficient data are included. Compute `ROUND(AVG(uptime), 2)` for the metric.

#### Step 2: Rank with DENSE_RANK

`DENSE_RANK() OVER (ORDER BY avg_uptime DESC)` assigns identical ranks to ties and never skips rank numbers.

#### Step 3: Filter to top 3 ranks

Wrap in a subquery and filter `WHERE rnk <= 3`. This may return more than 3 rows if ties exist at rank 3.

---

### The solution

**Aggregate, rank, and filter top ranks**

```sql
SELECT svc_name, avg_uptime
FROM (
    SELECT
        svc_name,
        ROUND(AVG(uptime), 2) AS avg_uptime,
        DENSE_RANK() OVER (ORDER BY ROUND(AVG(uptime), 2) DESC) AS rnk
    FROM svc_health
    GROUP BY svc_name
    HAVING COUNT(*) >= 5
) ranked
WHERE rnk <= 3
ORDER BY avg_uptime DESC
```

> **Cost Analysis**
>
> The `GROUP BY` reduces 30M rows to at most 120 (distinct services). The `HAVING` filter and window sort operate on this tiny result set. The bottleneck is the initial full scan of `svc_health`.

> **Interviewers Watch For**
>
> Applying the `HAVING` filter correctly before ranking. Candidates who filter after ranking may accidentally rank services with insufficient data and then exclude them, producing incorrect top-3 results.

> **Common Pitfall**
>
> Using `RANK()` instead of `DENSE_RANK()` would skip rank numbers after ties (e.g., 1, 1, 3 instead of 1, 1, 2). The prompt explicitly says "no gaps".

---

## Common follow-up questions

- What if you needed the bottom 3 instead of the top 3? _(Tests ability to flip `ORDER BY` direction inside the window function.)_
- How would you handle it if `uptime` had NULL values? _(Tests knowledge that `AVG` ignores NULLs by default, but `COUNT(*)` vs `COUNT(uptime)` differs.)_
- Could you solve the minimum-5-checks filter without HAVING? _(A subquery pre-filter or CTE with a count column is an alternative approach.)_
- What if the dataset had 50,000 distinct services? _(The window sort after GROUP BY becomes non-trivial; discuss indexing or materialized views.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_services_by_uptime)
- [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.