# Services With Multi-Quarter Uptime

> Multi-quarter uptime streaks.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

The SRE team considers a service stable only if it recorded healthy checks across multiple quarters. Find services that had at least one healthy check (case-insensitive) in at least two separate quarters of 2026.

## Worked solution and explanation

### Why this problem exists in real interviews

SRE teams care less about a single green check and more about whether a service is consistently healthy across reporting windows. Interviewers use this prompt to see whether you can derive a quarter bucket from a TEXT date column, normalize a status string, scope to a single calendar year, and then count distinct buckets per service. Each step is a routine clause in isolation but the chain is where candidates trip.

---

### Break down the requirements

#### Step 1: Bucket each check into a calendar quarter

`checked` is TEXT, so reach for `strftime('%m', checked)` and `strftime('%Y', checked)` rather than date math. Cast the month to INTEGER, then a `CASE` over the integer ranges (`<= 3`, `<= 6`, `<= 9`, else) maps each row to 'Q1', 'Q2', 'Q3', or 'Q4'. Casting keeps the comparison numeric and avoids string ordering bugs.

#### Step 2: Filter to healthy checks in the target year

The prompt says 'healthy check (case-insensitive)' so wrap `status` in `LOWER()` and compare to `'healthy'`. Pin the year with `strftime('%Y', checked) = '2025'` (the engine fills in the reference year). Both filters belong in `WHERE` because they look at raw row columns, not aggregates.

#### Step 3: Count distinct quarters per service

Inside the CTE, `SELECT DISTINCT svc_name, qtr` collapses 60M rows down to one row per service per quarter. Outside, `GROUP BY svc_name HAVING COUNT(*) >= 2` keeps services that appear in two or more quarters. The `DISTINCT` is what makes `COUNT(*)` equal to 'distinct quarter count'.

---

### The solution

**Quarter buckets, then count distinct quarters per service**

```sql
WITH svc_quarters AS (
  SELECT DISTINCT svc_name,
         CASE WHEN CAST(strftime('%m', checked) AS INTEGER) <= 3 THEN 'Q1'
              WHEN CAST(strftime('%m', checked) AS INTEGER) <= 6 THEN 'Q2'
              WHEN CAST(strftime('%m', checked) AS INTEGER) <= 9 THEN 'Q3'
              ELSE 'Q4' END AS qtr
  FROM svc_health
  WHERE LOWER(status) = 'healthy'
    AND strftime('%Y', checked) = '2025'
)
SELECT svc_name
FROM svc_quarters
GROUP BY svc_name
HAVING COUNT(*) >= 2
ORDER BY svc_name
```

> **Cost Analysis**
>
> `svc_health` carries 60M rows partitioned by `checked` across 1095 daily partitions. The year filter prunes roughly two-thirds of partitions before any work happens. Only 200 distinct `svc_name` values exist, so the post-DISTINCT CTE is at most 800 rows (200 services times 4 quarters). The final aggregation runs on that tiny set.

> **Interviewers Watch For**
>
> Interviewers watch whether you reach for `LOWER()` instead of trusting the casing of `status`, whether you cast the month to INTEGER before comparing, and whether you anchor the year as a string against `strftime('%Y', ...)` rather than parsing the date. They also note whether you use `DISTINCT` inside the CTE versus a redundant `GROUP BY svc_name, qtr`.

> **Common Pitfall**
>
> Skipping the year filter pulls every quarter that ever appeared in the dataset, so a service with one healthy check in 2023-Q1 and another in 2024-Q4 passes the threshold even though it had nothing in 2025. Equally, dropping `DISTINCT` and using `COUNT(*) >= 2` against raw rows counts checks, not quarters, and almost every service trivially clears it.

---

## Common follow-up questions

- How would you change the query to require healthy checks in at least three distinct months of 2025 instead of two distinct quarters? _(Tests whether the candidate adapts the CASE buckets and the HAVING threshold without rewriting the whole query. They should change the bucketing to months and bump the threshold.)_
- Would `WHERE strftime('%Y', checked) = '2025'` versus `WHERE checked >= '2025-01-01' AND checked < '2026-01-01'` produce different scan plans on a partitioned table? _(Tests partition awareness. `svc_health` is partitioned by `checked`. A range predicate lets the planner prune partitions, while wrapping the column in `strftime` disables pruning on most engines.)_
- How would you extend the query to find services that were healthy in two or more quarters within the same `region`? _(Tests whether the candidate reasons about the 6 distinct `region` values and adds `region` to the GROUP BY so a service must clear the threshold per region.)_

## Related

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