The Steady Few

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

L4

Asked in

Capital One

Problem

Regions whose probes sometimes come back without a latency reading keep sliding down our reliability leaderboard, yet a check with no recorded latency should count as zero latency, and only a check with no recorded uptime has nothing to add. In svc_health, a check's effective hours is its uptime minus a tenth of its latency, and a region's score is the total of those, rounded to two decimals. Return the regions holding the top three places, most reliable first, with equal totals sharing a place.

Endurance is measured in the hours you hold the line. Rank the regions that keep it.

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

This is a NULL-safe sum dressed up as an SRE leaderboard, with a top three that counts places instead of rows. Everyone writes SUM(uptime - latency / 10.0) and groups by region. The trap is the check with no latency reading: the whole per-row term becomes NULL, SUM skips it, and the region loses that check's entire uptime, not just its latency penalty. On this seed that is 23 of 200 checks, four or five per region, each worth 90 to 100 hours, so every total on the board comes back low. The quieter half is the cut: 'top three places, equal totals sharing a place' is a statement about values, and a bare LIMIT 3 is only right while nobody ties for third.

Reading the requirements

Step 1: Score each check without losing it

A check's effective hours is uptime - latency / 10.0. When latency is NULL the subtraction is NULL, so wrap it as COALESCE(latency, 0) and the check keeps its full uptime. A check with no uptime stays NULL and SUM ignores it, which is what the ask wants: there is nothing to score.

Step 2: Total per region, round once at the end

Sum the per-check terms in one GROUP BY region pass. Keep the totals unrounded while choosing places, and apply ROUND(..., 2) only in the final projection, so the rounding cannot create or break a tie.

Step 3: Keep three places, then present them

DENSE_RANK() over the descending totals gives equal totals the same place, and rnk <= 3 keeps every region in places one to three. The filter chooses the rows but does not sort them, so a trailing ORDER BY total_effective_hours DESC lists them most reliable first.

The solution

WITH effective AS (
  SELECT region,
         SUM(uptime - COALESCE(latency, 0) / 10.0) AS total_effective_hours
  FROM svc_health
  GROUP BY region
),
ranked AS (
  SELECT region,
         total_effective_hours,
         DENSE_RANK() OVER (ORDER BY total_effective_hours DESC) AS rnk
  FROM effective
)
SELECT region, ROUND(total_effective_hours, 2) AS total_effective_hours
FROM ranked
WHERE rnk <= 3
ORDER BY total_effective_hours DESC

Common follow-up questions

Common follow-up questions

• How would you make this incremental so the dashboard refreshes hourly without rescanning 30M rows? (Probes a rollup keyed on region and hour of `checked`, and a merge strategy for late checks.)
• Is crediting a check with no `latency` its full uptime the right call, or should a timeout be penalized? (Tests whether you can defend the `COALESCE` choice and price an alternative, such as a fixed penalty.)
• What changes if the leaderboard must show the place number next to each region? (Checks that you would return `rnk` and explain why `DENSE_RANK` and `RANK` print different numbers after a tie.)