The Gap Between Neighbors

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

L5

Asked in

LinkedIn

Problem

Each row in svc_health is one health check, and the monitor records status in mixed case where every spelling counts as its own state, so only checks written exactly as 'healthy' qualify. Within a region, pair each qualifying check with every other healthy check whose uptime sits within 5 percentage points of it, counting two different checks as a pair even when they come from the same service. For each service, report the average absolute latency gap across every pair its checks take part in.

Same region, and still they disagree.

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

This is a same-region pairwise distance problem wearing a monitoring costume. svc_health self-joins on region with check_id != check_id, so every healthy check pairs with every other healthy check in its region, and the ABS(uptime diff) <= 5 predicate is an inequality join, not a row filter. Two quiet traps live here: the status column writes 'Healthy', 'healthy', and 'DEGRADED' as separate strings (only the exact lowercase 'healthy' counts), and the pairing grain is the individual check, not the service name, so two checks of the SAME service still pair with each other.

Break down the requirements

Step 1: Self-join the fact

Alias svc_health as s1 and s2. Join on same region and s1.check_id != s2.check_id so a check never pairs with itself, while two different checks of the same service still pair. Pairs appear twice (s1,s2) and (s2,s1), which is fine for an average.

Step 2: Apply pairwise predicates

Both sides need status = 'healthy' (exact case, no LOWER). The uptime band is ABS(s1.uptime - s2.uptime) <= 5. That predicate compares two rows, so it belongs in the join/WHERE, not a per-row filter.

Step 3: Aggregate from the left side

Group by s1.svc_name and take AVG(ABS(s1.latency - s2.latency)). The average is over every valid neighbor pair, weighted by how many neighbors each check has. A pair where either latency is NULL yields a NULL gap that AVG silently skips.

The solution

SELECT s1.svc_name,
       AVG(ABS(s1.latency - s2.latency)) AS avg_latency_diff
FROM svc_health s1
JOIN svc_health s2
  ON s1.region = s2.region
 AND s1.check_id != s2.check_id
WHERE s1.status = 'healthy'
  AND s2.status = 'healthy'
  AND ABS(s1.uptime - s2.uptime) <= 5
GROUP BY s1.svc_name;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you cut self-join cost on 70M rows? (Pre-aggregate the latest healthy check per `(svc_name, region)` in a CTE, then self-join the reduced set. You lose intra-day pairs but kill the cardinality.)
• What if `uptime` is NULL for some checks? (`ABS(NULL - x) <= 5` is unknown, so those pairs drop. If the policy is 'treat NULL uptime as healthy', wrap in `COALESCE(uptime, 100)` or guard with `IS NOT NULL`.)
• How would you also return the neighbor count behind each average? (Add `COUNT(*) AS pair_count` to the SELECT. It exposes whether a small-neighborhood node's average is statistically thin.)