Health Checks per Service

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: Apple

Problem

Services with the same number of recorded health checks are listed alphabetically by name. Count every check svc_health holds for each service and list the services most-monitored first.

Some services get checked constantly.

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 15 rows
svc_name	check_count
auth-svc	18
cache-svc	18
cron-svc	18
edge-auth-svc	4
event-bus	4

Worked solution and explanation

This is a row tally with a sort that has to be total, dressed up as a monitoring-coverage audit. Anyone writes the grouping. What separates candidates is noticing two things about this data. Every row is one check, so the tally is COUNT(*), not a count of some column that happens to be filled: 23 checks have no latency, and COUNT(latency) quietly drops them. And the counts barely spread: ten services sit at 18 checks and five at 4, so a sort on the count alone leaves every row in a tie and the name decides the entire sequence.

Walk it

Step 1: Decide what one row is

Each svc_health row is one recorded check. The status column is messy ('Healthy' beside 'healthy', 'DEGRADED' beside 'degraded'), which tempts people to clean or filter it first. Coverage counts every check whatever it reported, so status never enters the query.

Step 2: Tally rows, not values

COUNT(*) counts rows; COUNT(latency) counts rows where latency is filled. The checks whose timing never landed are still checks, so the row count is the coverage number, aliased check_count.

Step 3: Make the order total

Sort by check_count DESC, then svc_name. With two distinct counts across fifteen services, the second key is doing almost all the ordering work, and it is the one people leave off.

Checks per service, most-monitored first

SELECT svc_name, COUNT(*) AS check_count
FROM svc_health
GROUP BY svc_name
ORDER BY check_count DESC, svc_name

One row per svc_name; the name settles every tie inside the 18 and 4 blocks.

Count alone: ORDER BY check_count DESC puts the 18s before the 4s and stops there. Inside each block the rows arrive in whatever order the hash aggregate emitted them, which can differ between runs and between engines.
Count, then name: ORDER BY check_count DESC, svc_name fixes every position: 'auth-svc', 'cache-svc', 'cron-svc' lead the 18s, and 'edge-auth-svc' and 'event-bus' lead the 4s.

Common follow-up questions

Coverage now has to be per service and per `region`. What changes in the grouping and in the tie rule? (Tests extending the grain while keeping the order total.)
The vendor only wants checks from the last 90 days of `checked`. Where does that filter go, and can a service vanish from the list? (Tests filtering before the tally and noticing services with no recent checks disappear.)
How would you also show each service's share of all checks as a percentage? (Tests a ratio against the overall total and integer division.)
What changes if some rows spell the service 'Auth-Svc' instead of 'auth-svc'? (Tests normalizing a grouping key before counting.)