# Service Reliability Tiers

> Reliability tiers. Based on uptime.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Classify services by uptime into reliability tiers: 99.9%+ is 'Platinum', 99.0 to 99.89% is 'Gold', 95.0 to 98.99% is 'Silver', below 95% is 'Bronze'. For each tier, show the minimum, average, and maximum latency. Exclude any checks where the status mentions 'maintenance'. Results should go from highest average latency to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

Drawn from a latency monitoring domain, this question centers on conditional aggregation via CASE over the `svc_health` table. The tricky part is handling columns like `status`, `latency`, and `uptime` correctly under the given constraints.

---

### Break down the requirements

#### Step 1: Filter with pattern matching on `NOT`

The `LIKE '%maintenance%'` pattern narrows the result to matching rows. The `%` wildcard matches any sequence of characters.

#### Step 2: Aggregate by `tier`

`GROUP BY tier` collapses rows to one per group. The aggregate functions (`SUM`, `COUNT`, `AVG`, etc.) compute the metric for each group.

#### Step 3: Sort the final output

The `ORDER BY` clause ensures the result appears in the expected sequence. Interviewers check that the sort direction matches the prompt.

---

### The solution

**Case pivot for service reliability tiers**

```sql
SELECT CASE WHEN uptime >= 99.9 THEN 'Platinum' WHEN uptime >= 99.0 THEN 'Gold' WHEN uptime >= 95.0 THEN 'Silver' ELSE 'Bronze' END AS tier, MIN(latency) AS min_latency, AVG(latency) AS avg_latency, MAX(latency) AS max_latency
FROM svc_health
WHERE status NOT LIKE '%maintenance%'
GROUP BY tier
ORDER BY avg_latency DESC
```

> **Cost Analysis**
>
> With ~30M rows, the GROUP BY reduces the working set before any downstream operations. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether the query returns exactly the columns and ordering the prompt specifies; how quickly you identify the core operation and write clean, minimal code.

> **Common Pitfall**
>
> Forgetting the `%` wildcard or placing it on the wrong side changes the match semantics entirely. `LIKE 'x%'` matches prefixes; `LIKE '%x'` matches suffixes.

---

## Common follow-up questions

- The `latency` column in `svc_health` has a 1% null rate. How does your query handle rows where `latency` is NULL, and could that silently change the result count? _(Tests whether the candidate accounts for NULLs in `svc_health.latency` and understands how aggregates skip NULL values.)_
- `svc_health.check_id` has roughly 30,000,000 distinct values. What index strategy would you use to avoid a full scan on `svc_health`? _(Tests indexing knowledge specific to the high-cardinality `check_id` column in `svc_health`.)_
- Your LIKE pattern may prevent index usage on `svc_health`. How would you restructure the filter to be index-friendly? _(Tests understanding of leading-wildcard LIKE and its impact on index scans.)_

## Related

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