Low Uptime Services

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: City and County of San Francisco

Problem

The SRE team set a 95% uptime SLA and needs to surface every health check that fell short, worst uptime first so the shakiest services surface at the top. Show the service name, check date, and uptime for each violation, and when two share the same uptime, put the one checked earliest ahead.

Underperforming services.

Tables

svc_health

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

svc_health: 10 of 200 rows
check_id	svc_name	status	latency	uptime	checked	region
137	payment-api	degraded	3.6	97.03	2026-02-02 01:05:00	us-west-2
174	user-svc	unhealthy	6.7	94.06	2026-03-03 02:10:00	eu-west-1
211	search-api	timeout	9.8	91.09	2026-04-04 03:15:00	ap-south-1
248	gateway	Healthy	12.9	98.12	2026-05-05 04:20:00	eu-central-1
285	notif-svc	DEGRADED	16	95.15	2026-06-06 05:25:00	us-east-1
322	ml-serving	healthy	19.1	92.18	2026-07-07 06:30:00	us-west-2
359	cache-svc	degraded	NULL	99.21	2026-08-08 07:35:00	eu-west-1
396	queue-worker	unhealthy	25.3	96.24	2026-09-09 08:40:00	ap-south-1
433	cron-svc	timeout	28.4	93.27	2026-01-10 09:45:00	eu-central-1
470	auth-svc	Healthy	31.5	90.3	2026-02-11 10:50:00	us-east-1

Expected output

First 5 of 88 rows
svc_name	checked	uptime
payment-api	2022-01-05 00:00:00	88
payment-api	2022-01-21 12:00:00	88
notif-svc	2026-01-17 00:48:00	88.2
cron-svc	2025-01-01 00:36:00	88.4
search-api	2024-01-13 00:24:00	88.6

Worked solution and explanation

This is a units question dressed up as an SLA report. The SLA reads '95%', and the reflex is to type uptime < 0.95. But uptime is stored on a 0-100 scale: the very first sample rows read 97.03, 94.06, 91.09. Write the fraction and the filter matches nothing, so the report comes back empty and every breach goes unreported. Anyone can write a filter and a sort; what separates candidates is glancing at the data before typing the threshold.

Read the units, then write the bar

Step 1: Check the scale of `uptime` before trusting the prose

A percentage can be stored as 0.9703 or as 97.03, and the prompt will not tell you which. Three sample values settle it: everything in svc_health sits between about 88 and 99.99, so 95% means the literal 95.

Step 2: Keep strict misses only

A check at exactly 95 meets the SLA, so the bar is uptime < 95, not <=. The 15 checks with a NULL uptime fall out on their own because a comparison with NULL is never true. That is the right outcome: a check that recorded no uptime is not a proven breach.

Step 3: Sort worst first, earliest first within a tie

ORDER BY uptime ASC puts the deepest breach on top, and checked ASC orders checks that share an uptime. The second key does real work here: two 'payment-api' checks both sit at 88.0. checked is text in 'YYYY-MM-DD HH:MM:SS' form, so sorting it as text is already chronological.

SLA violations, worst first

SELECT svc_name, checked, uptime
FROM svc_health
WHERE uptime < 95
ORDER BY uptime ASC, checked ASC

Fraction reading: WHERE uptime < 0.95 treats '95%' as 0.95. No stored value is that low, so the query returns zero rows and looks like a clean month.
Percentage reading: WHERE uptime < 95 matches the column's real scale and returns all 88 breaches, from 88.0 upward.

Common follow-up questions

The SLA becomes monthly: which services averaged under 95 for a calendar month? (Moves the bar from single checks to an average per `svc_name` and month.)
Ops wants checks with a `NULL` `uptime` listed as suspected breaches. What changes? (Tests whether you see that the current filter drops them silently.)
Return only the single worst check per service. (Picking one row per `svc_name` with a deterministic tie rule on `checked`.)