7-Day Rolling Lab Comparison Query

Write a SQL query using window functions to calculate each patient's 7-day rolling average lab value and compare it to their prior week.

Use lab_measurements as the source table. Treat multiple measurements on the same patient and date as one daily average, and preserve dates whose lab values are all NULL.

Output

1. One row per patient and measurement date, ordered by patient_id, then measurement_date.
2. Columns: patient_id, measurement_date, daily_lab_value, rolling_7_day_avg, prior_week_rolling_avg, change_from_prior_week, and comparison.
3. Compare each rolling average with the value exactly seven calendar days earlier. Use no_prior_week when that date is unavailable.