# The Gap Between Neighbors > Same region, and still they disagree. Canonical URL: 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. ## Tables ### svc_health - check_id INTEGER - svc_name TEXT - status TEXT - latency REAL - uptime REAL - checked TEXT - region TEXT Sample rows (10 of 200): | check_id | svc_name | status | latency | uptime | checked | region | | --- | --- | --- | --- | --- | --- | --- | | 137 | payment-api | degraded | 3.6 | 97.03 | 2026-02-02 01:05:00 | us-west-2 | | 174 | user-svc | unhealthy | 6.7 | 94.06 | 2026-03-03 02:10:00 | eu-west-1 | | 211 | search-api | timeout | 9.8 | 91.09 | 2026-04-04 03:15:00 | ap-south-1 | | 248 | gateway | Healthy | 12.9 | 98.12 | 2026-05-05 04:20:00 | eu-central-1 | | 285 | notif-svc | DEGRADED | 16 | 95.15 | 2026-06-06 05:25:00 | us-east-1 | | 322 | ml-serving | healthy | 19.1 | 92.18 | 2026-07-07 06:30:00 | us-west-2 | | 359 | cache-svc | degraded | NULL | 99.21 | 2026-08-08 07:35:00 | eu-west-1 | | 396 | queue-worker | unhealthy | 25.3 | 96.24 | 2026-09-09 08:40:00 | ap-south-1 | | 433 | cron-svc | timeout | 28.4 | 93.27 | 2026-01-10 09:45:00 | eu-central-1 | | 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 | ## Expected output First 5 of 15 rows: | svc_name | avg_latency_diff | | --- | --- | | auth-svc | 183.05000000000004 | | cache-svc | 489.9200000000001 | | cron-svc | 523.7333333333332 | | edge-auth-svc | 201.21818181818182 | | event-bus | 208.95 | ## 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. > **Match the status string literally** > > The instinct is to write `LOWER(status) = 'healthy'` so 'Healthy' and 'healthy' both pass. That folds rows the expected answer treats as distinct states into the average, and every per-service number drifts. The data deliberately mixes case so you have to decide consciously: here, equality is literal, so match the exact string 'healthy' on BOTH aliases. > **Exclude by `check_id`, not `svc_name`** > > The second trap is the join key for self-exclusion. Excluding pairs with `s1.svc_name != s2.svc_name` looks reasonable for 'every other node', but it drops every pair between two checks of the same service, and the averages for any multi-check service collapse. The correct exclusion is `s1.check_id != s2.check_id`: only a check paired with itself is removed; same-service checks still pair. --- ### 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 **AVG PAIRWISE LATENCY DIFF PER SERVICE** ```sql 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; ``` > **Only `region` narrows the join** > > A self-join on a 70M-row table is quadratic worst case. The `region` equality is the hash key; `ABS(uptime diff) <= 5` and `check_id != s2.check_id` are residual filters that the planner can't accelerate. Sub-region the join key in production. > **Both aliases, check grain, literal case** > > Three things. Whether you put `status = 'healthy'` on both aliases (filtering only `s1` lets non-healthy `s2` rows leak into the average). Whether you exclude self-pairs by `check_id` rather than `svc_name` (the latter silently drops same-service pairs). And whether you reach for `LOWER()` and quietly admit 'Healthy' rows the spec excludes. The strong candidate asks about case and pairing grain before writing the predicate. > **Keeping one direction per pair moves the averages** > > Writing `s1.check_id < s2.check_id` to dedupe pairs changes the average if neighbor counts differ across checks. The expected query keeps both directions, so each check's neighborhood is fully represented. --- ### 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_gap_between_neighbors) - [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.