# The High and the Low

> The fastest and slowest in every region.

Canonical URL: <https://datadriven.io/problems/the_high_and_the_low>

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

For each region, surface the highest and lowest latency services, excluding any region whose name contains 'test'. If services tie for the top or bottom spot, include all of them.

## 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 all sitting at the same latency loses two of them. The second trap is the filters. The 'test' regions and the NULL latency rows have to go before you rank, or a NULL sorts to one end of the order and quietly steals an extreme.

---

### Break down the requirements

#### Step 1: Filter out test regions and NULL latencies

Filter svc_health to drop any region whose name contains 'test' and drop rows with NULL latency (a NULL latency cannot be the highest or lowest, and it can sort to an edge of the ordering).

#### Step 2: DENSE_RANK twice in the same SELECT

In a CTE, compute two DENSE_RANK windows per region: one ORDER BY latency DESC (high_rank) and one ORDER BY latency ASC (low_rank), so tied extremes all share rank 1.

#### Step 3: Keep ties, label, and sort

Keep rows where high_rank = 1 or low_rank = 1, labeling them 'highest' or 'lowest' via CASE, then sort for a stable output.

---

### The solution

**Double rank, then filter to the extremes**

```sql
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 NOT LIKE '%test%'
    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
ORDER BY region, latency_type, svc_name
```

> **Cost Analysis**
>
> There is no pre-aggregation here: the two DENSE_RANK windows scan svc_health directly. What keeps it cheap is filtering first. region NOT LIKE '%test%' and latency IS NOT NULL run before the window sort, so the ranking operates on fewer rows. On 50M rows the planner still sorts each region partition by latency twice, once DESC and once ASC, but region has only about 8 distinct values, so each partition is large and few, and the final single ORDER BY runs over the tiny set of surviving extremes.

> **Interviewers Watch For**
>
> Whether you used DENSE_RANK to keep ties (the prompt explicitly says 'tie'), whether you filtered region in WHERE rather than HAVING, and whether your 'test' exclusion would actually catch 'TEST' and 'Test'. LIKE is case sensitive for the default ASCII path in SQLite, so lowercasing both sides is safer than a bare NOT LIKE when casing is uncertain.

> **Common Pitfall**
>
> Using ROW_NUMBER instead of DENSE_RANK silently drops services that tie at the extreme. A region with three services all at 100ms latency should return all three as 'highest' and all three as 'lowest'; ROW_NUMBER returns only one of each and you never see the difference until production data has ties.

---

## 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' and never 'lowest'. The candidate should surface the ambiguity and propose a 'both' label or two rows.)_
- Why filter region in WHERE rather than after grouping in HAVING? _(Tests filter pushdown. region is a row level column, not an aggregate, so WHERE is correct and shrinks the input before the windows run. HAVING would force a grouping step the query does not otherwise need.)_
- 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'; an additional latency >= 0 predicate in WHERE would defend against ranking a buggy reading as the fastest.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_high_and_the_low)
- [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.