Rolling 7-Day Yield Window

Write a SQL query using window functions to calculate a rolling 7-day average of daily bioprocess yield by customer and process stage.

Use the bioprocess_yields table. Calculate daily yield from all measurements recorded for the same customer, process stage, and date. Include dates with recorded measurements, including dates whose measurements are all NULL.

Output

1. One row per customer, process stage, and measurement date
2. Columns: customer_name, process_stage, yield_date, daily_yield, and rolling_7_day_avg
3. The rolling average includes the current date and the preceding six calendar days, ordered by customer, process stage, and date