Quick Overview

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.

Compute a Seven-Day Rolling Average

Company: Fetch Rewards

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. |

Overview: 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.

Read the full Fetch Rewards Data Scientist interview experience this question came from

For the metric named 'daily_active_users', compute the 7-day rolling average of metric_value for each available metric_date, using calendar-day windows and SQL window functions.

Tables

daily_metric_values(metric_date DATE, metric_name VARCHAR, metric_value NUMERIC)

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.

Community answers

Answer by tmm86nn

-- Write your SQL query here SELECT metric_date, metric_value, round(avg(metric_value) over (order by metric_date range between interval '6 days' preceding and current row),2) as rolling_7d_avg FROM daily_metric_values where metric_name = 'daily_active_users' order by metric_date

Loading coding console...