# The Blast Radius > A reliability scoreboard for deploy teams. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Google ## Problem Find each author's average latency score, rounded to two decimals and highest first, where every `svc_health` check logged for a service counts as one score for each `deploy_logs` deploy of that same service. The release-engineering team's scoreboard carries one row per person, however their name happened to be typed on the deploy record. ## Tables ### deploy_logs - log_id INTEGER - svc_name TEXT - version TEXT - env_name TEXT - status TEXT - deploy_at TEXT - dur_secs INTEGER - author TEXT Sample rows (10 of 200): | log_id | svc_name | version | env_name | status | deploy_at | dur_secs | author | | --- | --- | --- | --- | --- | --- | --- | --- | | 131 | payment-api | v1.0.2 | staging | failed | 2026-02-02 01:11:00 | 23 | bob | | 162 | user-svc | v1.1.0 | dev | rolled_back | 2026-03-03 02:22:00 | 36 | charlie | | 193 | search-api | 2.0.0 | canary | in_progress | 2026-04-04 03:33:00 | 49 | dana | | 224 | gateway | v2.1.0 | Production | Success | 2026-05-05 04:44:00 | 62 | eve | | 255 | notif-svc | 2.1.1 | STAGING | FAILED | 2026-06-06 05:55:00 | 75 | frank | | 286 | analytics | v3.0.0-rc1 | production | success | 2026-07-07 06:06:00 | 88 | Alice | | 317 | ml-serving | v1.0.3 | staging | failed | 2026-08-08 07:17:00 | NULL | BOB | | 348 | auth-svc | v1.0.1 | dev | Rolled_Back | 2026-09-09 08:28:00 | 114 | alice | | 379 | Payment-API | v1.0.2 | canary | in_progress | 2026-01-10 09:39:00 | 127 | bob | | 410 | user-svc | v1.1.0 | Production | Success | 2026-02-11 10:50:00 | 140 | charlie | ### 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 6 rows: | author | avg_score | | --- | --- | | eve | 385.42 | | alice | 381.49 | | frank | 379.86 | | bob | 372.76 | | dana | 361.85 | ## Worked solution and explanation ### What this really is This is a many-to-many fan-out feeding an average, with a dirty grouping key on top. Anyone can write the join and the `AVG`. What separates candidates is noticing two things before the query runs: `svc_name` is not a key on either side, so one deploy drags in every health check its service ever logged, and `author` stores the same human as 'Alice', 'alice' and 'BOB'. Group on the raw name and one person fractures into several rows, each holding a partial average, and the scoreboard lists people who do not exist. --- ### Working through it #### Step 1: Match deploys to their health checks Join `deploy_logs` to `svc_health` on `svc_name` so each deploy lines up with its service's health checks. Say out loud that this is many-to-many: roughly 100 services on both sides means one `deploy_logs` row can explode into thousands of joined rows. #### Step 2: Average `latency` and round it Each matched `svc_health` row contributes its `latency` as one score, and a `NULL` reading simply drops out of `AVG`. The average spans every check attached to every deploy the author shipped, which is what the prompt defines, even though it over-weights busy services. Wrap it in `ROUND(..., 2)`: the raw value carries float tails like 381.48749999 that will never match a two-decimal scoreboard. #### Step 3: Fold case variants with `LOWER()` The expected output shows only lowercase names, while the input holds 'Alice', 'alice', 'BOB' and 'bob'. Group and select on `LOWER(dl.author)` in both places, or the grain splits and every average lands on a fragment of a person. #### Step 4: Order by `avg_score`, highest first `ORDER BY avg_score DESC` puts the highest average on top. Every author lands on a different rounded `avg_score` here, so the single key is already a total order; add an `author` tiebreaker the day two people can tie. --- ### The solution **Cross-table average with case-folded authors** ```sql SELECT LOWER(dl.author) AS author, ROUND(AVG(sh.latency), 2) AS avg_score FROM deploy_logs dl JOIN svc_health sh ON dl.svc_name = sh.svc_name GROUP BY LOWER(dl.author) ORDER BY avg_score DESC ``` > **The join output is the bill** > > At production scale this joins 1M `deploy_logs` rows against 30M `svc_health` rows on `svc_name`, and with only ~100 services the intermediate result dwarfs both inputs. A hash join plus a partial aggregate per author keeps it linear in the joined-row count, which is the real cost driver; pre-aggregating `SUM(latency)` and `COUNT(latency)` per `svc_name` first shrinks the health side to 100 rows before the join and yields the same average. > **Look at the keys before you group** > > Whether you check `author` for case variants before writing `GROUP BY`, and whether you say unprompted that the average is per health check, not per deploy. Both are one glance at the sample rows away. > **Volume masquerades as latency** > > Authors who own high-traffic services dominate on row count, not on worse `latency`. If fairness matters, average `latency` per `svc_name` first, then average those per-service scores per author, so every service counts once. --- ## Common follow-up questions - How would you make the score fair when one service has far more health checks than another? _(Tests pre-aggregation and weighted-average strategies.)_ - What changes if `latency` is stored as text that sometimes holds non-numeric values? _(Tests safe casting and filtering non-numeric text before `AVG`.)_ - How would you also report the number of deploys per author? _(Tests adding `COUNT(DISTINCT dl.log_id)` alongside the average.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_blast_radius) - [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.