7-Day Moving Average

Problem

Schema: Visits(visit_date DATE, count INT). One row per day, no gaps. Return visit_date and the average of count over the trailing 7-day window (today plus 6 prior days).

Constraints

• Days are contiguous. Don't pad with zeros for early days; the first 6 rows are partial windows.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

SELECT visit_date,
       AVG(count) OVER (ORDER BY visit_date
                        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS avg7
FROM Visits
ORDER BY visit_date;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.