The Blast Radius

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

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.

A reliability scoreboard for deploy teams.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

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 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

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

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.)