Single Service Owners

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

Vesta Innovations

Problem

List the services whose checks in svc_health came from exactly two different regions, each with that region count and its average checks per region as an exact decimal (seven checks over two regions is 3.5), alphabetically and stopping at the first five. The reliability team's audit only counts checks logged since the start of 2024.

One owner, one service. Nobody else.

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 is really asking

Strip the audit costume and this is a regional-footprint measurement fenced inside a time window, with a type question hiding in the last column. Anyone can write the GROUP BY. What separates candidates is whether the footprint is the number of different region values rather than the number of check rows, and whether the per-region average survives division intact. COUNT(*) / COUNT(DISTINCT region) divides two integers, and integer division throws the remainder away: seven checks over two regions reports 3, not 3.5. Count rows and the narrow services vanish; skip the cast and every odd-count service ships a quietly wrong average.

Working through it

Step 1: Prune to the recent window

The audit only cares about checks from the start of 2024 onward, so WHERE checked >= '2024-01-01' runs before any counting. Both output numbers depend on it: older checks would add regions to the footprint and rows to the average.

Step 2: Aggregate by svc_name

GROUP BY svc_name collapses the surviving checks to one row per service, so every aggregate is computed across that service's recent checks only.

Step 3: Count distinct regions, not checks

Reach is COUNT(DISTINCT region), not COUNT(*). A service checked 18 times in 2 regions has a raw count of 18 and a region count of 2. Only the distinct count answers 'how many regions'.

Step 4: Make the average exact

Divide the check count by the region count, but make one side a decimal first with CAST(COUNT(*) AS REAL). Two integers divide to an integer in most SQL engines, so without the cast every odd total loses its half.

Step 5: Filter the aggregate, order, cap

HAVING COUNT(DISTINCT region) = 2 keeps the narrow services; it must be HAVING because the count does not exist until after grouping. ORDER BY svc_name is deterministic since names are unique per group, and LIMIT 5 drops the sixth qualifier.

The solution

SELECT svc_name,
       COUNT(DISTINCT region) AS region_count,
       CAST(COUNT(*) AS REAL) / COUNT(DISTINCT region) AS checks_per_region
FROM svc_health
WHERE checked >= '2024-01-01'
GROUP BY svc_name
HAVING COUNT(DISTINCT region) = 2
ORDER BY svc_name
LIMIT 5

Uncast

COUNT(*) / COUNT(DISTINCT region) with 7 checks and 2 regions returns 3. No error, no warning, just a smaller number.

Cast

CAST(COUNT(*) AS REAL) / COUNT(DISTINCT region) returns 3.5. Casting either operand promotes the whole division to decimal.

Common follow-up questions

• How would you widen this to services covering two or three regions? (Moves an equality threshold to a range in `HAVING`.)
• If the dashboard wanted the average to two decimal places, where would you round, and why not earlier? (Probes rounding as a presentation choice versus exact storage.)
• If some checks had a `NULL` `region`, how would that change each column? (Probes NULL handling inside `COUNT(DISTINCT region)` and `COUNT(*)`.)