Compute a Seven-Day Rolling Average

You are analyzing daily product metrics for the Fetch mobile app.

Table

daily_metric_values

Column	Type	Description
metric_date	DATE	Calendar date in the America/Chicago timezone. There is at most one row per metric_name per date. Some dates may be missing.
metric_name	VARCHAR	Name of the metric, such as daily_active_users, purchases, or revenue.
metric_value	NUMERIC	Value of the metric on that date.

Task

For the metric named 'daily_active_users', compute the 7-day rolling average of metric_value for each available metric_date.

Requirements

• The 7-day window should include the current date and the previous 6 calendar days.
• The calculation should be based on calendar-day distance, not row count, because some dates may be missing.
• Use SQL window functions.
• Return one row per available metric_date for 'daily_active_users'.

Expected output columns

Column	Description
metric_date	The date of the metric.
metric_value	The metric value on that date.
rolling_7d_avg	The average metric value from metric_date - 6 days through metric_date, inclusive.

Compute a Seven-Day Rolling Average

Table daily_metric_values

column	type	constraints
metric_date	DATE
metric_name	VARCHAR
metric_value	NUMERIC

Sample data:

metric_date	metric_name	metric_value
2023-07-01	daily_active_users	1000
2023-07-02	daily_active_users	1200
2023-07-04	daily_active_users	1300
2023-07-05	daily_active_users	800
2023-07-06	daily_active_users	900
2023-07-08	daily_active_users	1100
2023-07-09	daily_active_users	1500
2023-07-10	daily_active_users	1400
2023-07-11	daily_active_users	1600
2023-07-12	daily_active_users	1700
2023-07-01	purchases	250
2023-07-02	purchases	280
2023-07-01	revenue	5320.75
2023-07-02	revenue	5800.4

Expected output

For each available metric_date where metric_name = 'daily_active_users', return the metric_value and the average of metric_value across the 7-day period from metric_date - 6 days through metric_date (inclusive). Missing calendar dates contribute no rows to the average.

metric_date	metric_value	rolling_7d_avg
2023-07-01	1000	1000
2023-07-02	1200	1100
2023-07-04	1300	1166.67
2023-07-05	800	1075
2023-07-06	900	1040
2023-07-08	1100	1060
2023-07-09	1500	1120
2023-07-10	1400	1166.67
2023-07-11	1600	1216.67
2023-07-12	1700	1366.67

Hints

1. Filter to metric_name = 'daily_active_users' first so the window only considers that metric.
2. Use a window frame based on calendar days, not row count: RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW.
3. Order the window by metric_date to define the rolling direction.
4. Return only one row per available metric_date; missing dates are simply absent from the table and thus from the average.