7-Day Running Total SQL

MediumSQL · PostgreSQL

Problem

Highmark Health analysts need a daily view of enrollment activity to monitor short-term changes across coverage operations. The enrollment source may contain missing dates, so the rolling calculation must use calendar days rather than simply counting the previous seven rows.

Write a PostgreSQL query that calculates the 7-day running enrollment total for January 1 through January 10, 2025.

Requirements

Return enrollment_date, the daily enrollment count, and running_7_day_total.
Include the current date and the six preceding calendar days in each total.
Preserve dates with a NULL daily count, treating them as zero in the total.
Return only the requested January reporting period, ordered chronologically. Enrollment records before January 1 may be used when they fall within a seven-day window.

Schema

daily_enrollments

Column	Type	Description
enrollment_datePK	DATE	Date of enrollment activity
enrollment_count	INTEGER	Number of enrollments recorded on the date

Tablesdaily_enrollments

Write a PostgreSQL query that calculates the 7-day running enrollment total for January 1 through January 10, 2025.

Requirements

Return enrollment_date, the daily enrollment count, and running_7_day_total.
Include the current date and the six preceding calendar days in each total.
Preserve dates with a NULL daily count, treating them as zero in the total.
Return only the requested January reporting period, ordered chronologically. Enrollment records before January 1 may be used when they fall within a seven-day window.