7-Check Rolling Average

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

L5

Asked in

Goldman Sachs

Problem

Our rolling latency average for a health check covers that check and the six checks before it on the same service, so one service's history never bleeds into another's. From svc_health, list every check alongside its latency and that rolling average, services alphabetically and each service's checks oldest to newest.

Seven entries hold the trend.

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, latency REAL, checked TEXT, status TEXT, uptime REAL, region TEXT

Expected output

Worked solution and explanation

What this really is

This is a bounded trailing average dressed up as a reliability dashboard. Everyone slices by service and orders by time; what separates candidates is the frame. Stop at AVG(latency) OVER (PARTITION BY svc_name ORDER BY checked) and the engine silently applies its default frame, RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which averages everything since the service's first check. The first seven rows per service still match, so an eyeball check passes. Every row after that is a cumulative average where an old spike never ages out. The ten long-running services have 18 checks each on this data, so more than half of their rows come back wrong.

Step 1: Restart the history per service

PARTITION BY svc_name keeps 'the six checks before' inside one service. Leave it out and a 'gateway' check averages in whatever 'cron-svc' reported a minute earlier. The numbers look plausible and mean nothing.

Step 2: Order the frame by checked

checked is ISO text ('2026-02-11 10:50:00'), so string order is time order and no cast is needed. This ordering decides which rows count as 'before'. It is separate from the final ORDER BY that lays out the report.

Step 3: Pin the frame to seven rows

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW counts physical rows: this check plus at most six earlier ones. A service's first check sees a frame of one and its second a frame of two, so early rows average what exists instead of coming back blank. That is why the first 'auth-svc' row in the preview is just its own 279.5.

Step 4: Let AVG skip the missing latencies

23 of the 200 checks have no latency. AVG ignores them and divides by the values it did see, which is why the 2026-01-08 'auth-svc' row still shows 279.5 with its own latency blank. The row stays in the output; only its value drops out of the math.

SELECT
    svc_name,
    checked,
    latency,
    AVG(latency) OVER (
    PARTITION BY svc_name
    ORDER BY checked
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS rolling_avg
FROM svc_health
ORDER BY    svc_name, checked

Default frame

AVG(latency) OVER (PARTITION BY svc_name ORDER BY checked) averages from the first check to the current one. Row 18 of a service averages all 18 checks, so a January spike still weighs on September.

Explicit seven-row frame

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW slides. Row 18 averages rows 12 to 18, and the January spike leaves the figure once seven newer checks arrive.

Common follow-up questions

• What if the window were the last 24 hours instead of the last seven checks? (Moves from a row-count frame to a time-range frame on a real timestamp type.)
• How would you blank the average until a service has seven checks with a latency? (A `COUNT(latency)` over the same frame plus a conditional on it.)
• Two checks for one service land on the same `checked` value. Is the result still deterministic? (Adding `check_id` as a tiebreaker inside the frame ordering.)
• How would you keep this current as checks stream in? (Holding the last seven values per service as state instead of re-sorting history.)