Lowest Latency per Service

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

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

Problem

For each service in the us-east-1 region, find the lowest latency recorded.

The fastest response each service ever gave.

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 8 rows
svc_name	min_latency
auth-svc	31.5
edge-auth-svc	80
event-bus	110
feature-flag-svc	170
notif-svc	16

Worked solution and explanation

Why this problem exists in real interviews

The svc_health table contains svc_name and status values that must be processed with grouping. This appears in mid-level screens to probe whether you reason about the correct aggregation grain before writing any window or GROUP BY clause.

Break down the requirements

Step 1: Filter to the target rows

Apply the WHERE filter to restrict the working set before aggregation. Filtering early reduces the number of rows that downstream operations process.

Step 2: Aggregate with MIN

Group by the output grain and apply MIN() to compute the metric. The GROUP BY must match exactly what the output needs: one row per group key.

Step 3: Order the final output

Apply ORDER BY as specified to produce the expected row sequence. When tied values exist, add a secondary sort column for determinism.

The solution

Filtered MIN with compound ORDER BY

SELECT svc_name, MIN(latency) AS min_latency
FROM svc_health
WHERE region = 'us-east-1'
GROUP BY svc_name

Common follow-up questions

What happens to your result if `svc_health`.latency contains NULLs for some rows? (Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on latency.)
How would you verify that your aggregation on `svc_health`.check_id is not double-counting due to duplicate rows? (Tests data quality awareness and deduplication strategies.)
With millions of distinct values in `svc_health`.check_id, what index strategy would you use to keep this query performant? (Tests indexing knowledge specific to high-cardinality columns like `check_id`.)