The High and the Low

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

KPMG

Problem

Which services in svc_health posted the highest and the lowest latency in each US region, labeled 'highest' or 'lowest' by the end they sit at? The on-call engineer reviewing them wants every service tied at an end, not just one of them.

The fastest and slowest in every region.

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 two-headed extremes query wearing an SRE dashboard costume. The real skill: can you pull both the slowest and the fastest service per region in a single pass, and keep every service that ties at either edge? Anyone can sort and grab the top row. The trap is ties: reach for ROW_NUMBER and a region with three services at the same latency loses two of them. The second trap is the filters. The non-US regions and the NULL latency rows have to go before you rank, or a NULL sorts to the front of the ascending order and quietly steals the 'lowest' slot.

Break down the requirements

Step 1: Scope to US regions and measured checks

Keep only rows of svc_health whose region starts with 'us-', and drop rows where latency is NULL. A NULL latency is not a measurement, and SQLite sorts it first under an ascending order, so leaving it in hands it the low end.

Step 2: DENSE_RANK twice in the same SELECT

In a CTE, compute two DENSE_RANK() windows per region: one ordering latency DESC (high_rank) and one ordering latency ASC (low_rank). Tied extremes all share rank 1, which is the whole point.

Step 3: Keep the ends and label them

Keep rows where high_rank = 1 or low_rank = 1, and let a CASE turn the ranks into the 'highest' or 'lowest' label. No final sort: the question asks which services, not in what order.

The solution

WITH ranked AS (
  SELECT svc_name, region, latency,
         DENSE_RANK() OVER (PARTITION BY region ORDER BY latency DESC) AS high_rank,
         DENSE_RANK() OVER (PARTITION BY region ORDER BY latency ASC)  AS low_rank
  FROM svc_health
  WHERE region LIKE 'us-%'
    AND latency IS NOT NULL
)
SELECT svc_name, region, latency,
       CASE WHEN high_rank = 1 THEN 'highest' WHEN low_rank = 1 THEN 'lowest' END AS latency_type
FROM ranked
WHERE high_rank = 1 OR low_rank = 1

Common follow-up questions

• How would you label a region's only service, which is both highest and lowest? (Tests edge case thinking. When both ranks are 1 the `CASE` checks `high_rank` first, so a sole service always labels as 'highest'. A strong answer surfaces the ambiguity and proposes a 'both' label or two rows.)
• Why filter on `region` before ranking rather than on the ranked output? (Tests filter pushdown. `region` is a row level column, so `WHERE` shrinks the input before the windows run; filtering after ranking wastes the sorts on rows you discard.)
• What changes if `latency` could be negative due to clock skew? (Tests data quality awareness. A negative `latency` from clock skew would sort to the bottom and be crowned 'lowest'; a `latency >= 0` guard in `WHERE` keeps a buggy reading out.)