Services With Multi-Quarter Uptime

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Meta

Problem

A service earns a stable rating in the SRE team's 2026 review only when its healthy checks in svc_health are spread across at least two separate calendar quarters of that year. List the services that earn it.

Multi-quarter uptime streaks.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this really is

This is a distinct-bucket count dressed up as an SRE stability review. The skill being probed: can you turn a timestamp into a quarter and count how many separate quarters each service covers, instead of how many checks it logged? Anyone can filter svc_health to healthy rows. The trick is collapsing to one row per service and quarter before counting. Skip that and a service with two healthy checks in the same week gets certified as stable for the year.

Build it in order

Step 1: Scope to healthy checks in 2026

Keep rows whose status is 'healthy' and whose EXTRACT(YEAR FROM checked) equals 2026. The year scope has to come before the bucketing, or Q1 of last year looks identical to Q1 of this year.

Step 2: Bucket each check into its quarter

EXTRACT(QUARTER FROM checked) returns 1 to 4 straight from the timestamp. No month arithmetic, no lookup: the quarter becomes a real column you can deduplicate on.

Step 3: Collapse to one row per service and quarter

SELECT DISTINCT svc_name, quarter in the healthy_quarters CTE means ten checks in March become one row. After that, COUNT(*) per service IS a count of quarters, and HAVING COUNT(*) >= 2 is the stability bar.

The solution

WITH healthy_quarters AS (
  SELECT DISTINCT
         svc_name,
         EXTRACT(QUARTER FROM checked) AS quarter
  FROM svc_health
  WHERE status = 'healthy'
    AND EXTRACT(YEAR FROM checked) = 2026
)
SELECT svc_name
FROM healthy_quarters
GROUP BY svc_name
HAVING COUNT(*) >= 2;

Counts checks

Filter healthy 2026 rows, GROUP BY svc_name, HAVING COUNT(*) >= 2. Two healthy checks on the same afternoon pass.

Counts quarters

Deduplicate to svc_name, quarter first, then HAVING COUNT(*) >= 2. Only two different quarters pass. COUNT(DISTINCT quarter) without the CTE is the same skill.

Common follow-up questions

• How would you require healthy checks in at least three different months of 2026 instead? (Swap the bucket to the month and raise the threshold; the shape of the query stays.)
• Would `EXTRACT(YEAR FROM checked) = 2026` and `checked >= '2026-01-01' AND checked < '2027-01-01'` scan the same partitions? (Partition pruning: a function on `checked` usually blocks it, a range on the raw column does not.)
• How would you find services that were healthy in two or more quarters within the same `region`? (Add `region` to both the CTE and the grouping so the bar is cleared per region.)