Compute a Seven-Day Rolling Average
Company: Fetch
Role: Data Scientist
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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. |
Quick Answer: This question evaluates proficiency with time-series aggregation and windowing concepts, specifically computing calendar-based 7-day rolling averages for a daily metric and using SQL window functions while handling missing dates.