Where You Stand

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

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

Glassdoor

Problem

The reliability team wants each service's latency sitting next to the baseline for its region. For every health check, return the service name, region, its latency, and the average latency across all services in that same region, laid out as a per-region readout: regions in alphabetical order, services alphabetical within each region, and each service's fastest check first.

Each service versus its region's average.

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 problem is really about

This is a group baseline hung on every detail row, dressed up as latency monitoring. The skill is giving each of the 200 health checks its region's average latency while the check keeps its own row and its own latency. Everyone reaches for AVG. What separates candidates is where the average lives. A GROUP BY region crushes 200 checks into five region rows and throws away svc_name and every individual reading. That is exactly the comparison the prompt asked you to keep.

Break down the requirements

Step 1: Fix the output grain first

One row per health check, not one per region. Say that before writing anything. It rules out collapsing the table and tells you the average has to travel to the rows.

Step 2: Attach the regional average

AVG(latency) OVER (PARTITION BY region) computes one average per region and copies it onto every row in that region. With no ORDER BY inside the window, the frame is the whole region, so every check in 'eu-west-1' sees the same number.

Step 3: Leave the missing readings in

23 checks have no latency. Do not filter them out: they are still checks and they belong in the readout. AVG skips NULL on its own, so the baseline is the mean of the readings that exist, and those rows show an empty latency next to it.

Step 4: Order the readout

Sort by region, then svc_name, then latency ascending so each service's fastest check comes first. An ascending sort here puts a missing latency ahead of the readings, which is why the preview shows the empty 'cache-svc' row above its 558.6 check.

The solution

SELECT svc_name, region, latency, AVG(latency) OVER (PARTITION BY region) AS avg_region_latency FROM svc_health ORDER BY region, svc_name, latency

SELECT region, AVG(latency) AS avg_region_latency
FROM svc_health
GROUP BY region

Collapse to the region

GROUP BY region returns five rows. The numbers are right, but there is nothing left to compare them against.

Carry the region to the row

AVG(latency) OVER (PARTITION BY region) returns all 200 rows, each with its own latency and the same regional figure beside it.

Common follow-up questions

• What does your regional average return for a region whose `latency` values are all `NULL`? (Checks whether the candidate knows `AVG` over nothing but `NULL` returns `NULL`, not zero.)
• How would you compute the regional average with a grouped subquery joined back, and what does that cost you? (Checks whether the candidate can reason about the plan difference between the two shapes at scale.)
• How would you also return each check's `latency` as a percentage of its regional average? (Checks whether the candidate can build on the attached baseline for a relative comparison.)