# The Steady Few > Endurance is measured in the hours you hold the line. Rank the regions that keep it. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem An SRE team is compiling a reliability leaderboard by region, where a probe's effective hours is its uptime minus a tenth of its latency, and a probe with no recorded latency counts as having zero latency. Total each region's effective hours and surface the three most reliable regions, with equal totals sharing a place. ## Worked solution and explanation ### What this problem really is This is an additive per-region sum with a top-three-that-survives-ties twist, wearing an SRE costume. The real skill: aggregate a derived per-probe metric into region totals, then take the highest three in a way that keeps ties and preserves order. Two quiet traps decide it. A probe with no latency reading turns `uptime - latency / 10.0` into NULL and vanishes from the sum, dragging its whole uptime out with it. And DENSE_RANK chooses the right rows but does not sort them, so without a trailing ORDER BY the output can come back in any physical order. --- ### Reading the requirements #### Step 1: Per-probe effective hours Each probe's effective hours is `uptime - latency / 10.0`. The `10.0` keeps the division in floating point. A timed-out probe can have no latency reading, and `uptime - NULL / 10.0` collapses the entire term to NULL, so wrap it in `COALESCE(latency, 0)` to credit that probe its full uptime at zero latency. #### Step 2: Total per region Group by `region` and sum the per-probe terms. The metric is additive, so a slow probe simply lowers its own contribution; on this data no per-row clamp is needed, only correct NULL handling before the sum. #### Step 3: Top three, ties sharing a place, then ordered `DENSE_RANK` over the descending region totals, then keep positions three or better, so a tie at the cutoff returns every tied region instead of silently dropping one. DENSE_RANK selects the rows; add a trailing `ORDER BY total_effective_hours DESC` so they are presented highest first. --- ### The solution **WHERE THE LIGHTS STAY ON** ```sql 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 ``` > **A NULL latency erases a whole probe** > > A NULL latency makes `uptime - latency / 10.0` evaluate to NULL, and a NULL term drops out of `SUM`, so a probe that timed out before recording latency takes its entire uptime with it and quietly shrinks the region total. `COALESCE(latency, 0)` keeps the probe in the sum at full uptime. Decide this on purpose; do not let it happen by accident. > **Selecting rows is not sorting them** > > A DENSE_RANK filter selects the right three regions, but selection is not ordering. Without the trailing `ORDER BY`, the engine is free to hand back the surviving rows in any physical order, so a reader comparing against a descending preview sees a mismatch that has nothing to do with the math. Rank to choose the rows; ORDER BY to present them. > **Interviewers watch for** > > Whether you reach for `LIMIT 3` or `DENSE_RANK`. LIMIT 3 quietly truncates a three-way tie at the cutoff to whatever rows the engine returns first. The ask says equal totals share a place, so the tie-aware form is the tell that you read the requirement. > **Cost analysis** > > 30M rows, no date filter. Every partition of `checked` contributes to some region total, so partition pruning buys nothing here; expect a full table scan feeding a hash aggregate over just six region groups. A pre-aggregated `region_uptime_daily` rollup would turn this into a small scan for a dashboard that refreshes on a schedule. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you make this incremental so the SRE dashboard refreshes hourly without rescanning 30M rows? _(Probes whether you can design a rollup table keyed on (region, checked_hour) and a merge strategy.)_ - Is crediting a NULL-latency probe full uptime the right call, or should a timed-out probe be penalized? _(Tests whether you can defend the COALESCE choice and reason about an alternative, such as charging a fixed latency penalty for a timed-out probe.)_ - Could you do this in a single query without CTEs, and would you? _(Checks whether you can produce a window-and-filter one-shot and articulate the readability tradeoff.)_ > **Why 10.0 and not 10** > > If `latency` were an INTEGER column, `latency / 10` would truncate toward zero in most engines: a latency of 7 becomes 0, not 0.7, and the effective hours overstate by the truncation residue. The decimal literal `10.0` forces a float promotion, which is the safe habit even when the column is already REAL. ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_steady_few) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.