# Services at Median Uptime

> Exactly at the median. Not above, not below.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The SRE team is profiling services that sit exactly at the median uptime level, since those are the best candidates for targeted improvement. Find any services whose uptime matches the median uptime value exactly.

## Worked solution and explanation

### Why this problem exists in real interviews

The core skill being tested is NTILE for bucket distribution, applied to the `svc_health` table in a reliability engineering context. Getting the `svc_name` and `uptime` columns right is where most candidates slip.

> **Trick to Solving**
>
> Finding the 'second highest' or 'runner-up' is a classic pattern. Avoid ORDER BY/LIMIT when precision matters.
> 
> 1. Use a scalar subquery to find the maximum
> 2. Filter the outer query to values strictly less than that maximum
> 3. Take the MAX of the filtered set

---

### Break down the requirements

#### Step 1: Isolate the intermediate result in a CTE

The `ranked` CTE computes the intermediate aggregation that the outer query builds on. This separation keeps each layer focused on a single task.

#### Step 2: Select the target columns

The SELECT clause picks exactly the columns the prompt asks for. Returning extra columns or missing a required alias would fail the grading check.

---

### The solution

**Ntile bucketing for services at median uptime**

```sql
WITH ranked AS (
    SELECT svc_name, uptime, NTILE(2) OVER (ORDER BY uptime) AS half
    FROM svc_health
)
SELECT svc_name, uptime
FROM ranked
WHERE half = 1 AND uptime = (SELECT MAX(uptime) FROM ranked WHERE half = 1)
```

> **Cost Analysis**
>
> With ~25M rows, the window function runs on the reduced set after filtering and grouping; CTEs materialize intermediate results, which can be beneficial or costly depending on the engine. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether you decompose the problem into named, testable stages rather than nesting everything; whether you use a subquery or self-join, and can explain the tradeoffs.

> **Common Pitfall**
>
> Returning extra columns that the prompt did not ask for, or using the wrong column alias, causes a grading mismatch even when the logic is correct.

---

## Common follow-up questions

- If `latency` in `svc_health` is NULL for some rows, how would your aggregation or join logic be affected? _(Probes understanding of NULL propagation through joins and aggregate functions on `svc_health.latency`.)_
- If `svc_health` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `latency`? _(Tests ability to identify performance hotspots related to `svc_health.latency` at scale.)_
- Does your database engine materialize the CTE or inline it? How would that affect repeated scans of `svc_health`? _(Tests understanding of CTE materialization semantics.)_

## Related

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