Calculate rolling 7-day average per region

Question

Write a query to compute rolling 7-day average grouped by date, handling edge cases like nulls and duplicates.

Codemia · Accepted Answer

The data involved includes a table that records daily metrics, likely with columns such as `date`, `region`, and `value`. The task is to compute a rolling 7-day average of the `value` for each `region`, grouped by `date`. This requires understanding the time series nature of the data and how to aggregate it effectively over the specified window.

1. **Identify the table** containing the relevant data, which we'll call `daily_metrics`.
2. **Use a Common Table Expression (CTE)** or subquery to calculate the daily total per region, ensuring we handle duplicates by using `SUM(value)`.
3. **Apply the window function** to compute the rolling 7-day average, using `AVG(value) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)`.
4. **Filter out nulls** in the dataset to ensure accurate averages.
5. **Select the final output** with the appropriate fields.

Here is the complete SQL query to solve the problem:
```sql
WITH daily_totals AS (
    SELECT 
        date, 
        region, 
        SUM(value) AS total_value
    FROM 
        daily_metrics
    WHERE 
        value IS NOT NULL
    GROUP BY 
        date, region
)
SELECT 
    date, 
    region, 
    AVG(total_value) OVER (PARTITION BY region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day_avg
FROM 
    daily_totals
ORDER BY 
    date, region;
```

To optimize this query for large datasets, consider the following strategies:
1. **Indexing**: Create indexes on `date` and `region` columns to speed up the filtering and grouping operations.
2. **Partitioning**: If the dataset is extremely large, consider partitioning the table by `region` or `date` to improve query performance.
3. **Materialized Views**: If this calculation is frequently needed, a materialized view can store the results of the rolling average, reducing computation time on subsequent queries.

For this problem, we must handle specific edge cases:
1. **NULL values**: Ensure that the query filters out any rows with null `value` to avoid skewing the average.
2. **Duplicates**: If multiple entries exist for the same `date` and `region`, using `SUM(value)` in the CTE will aggregate these entries correctly.
3. **Insufficient Data**: If there are less than 7 days of data for a particular region, the rolling average will naturally return NULL; this should be accepted as part of the output.

Calculate rolling 7-day average per region

Splunk

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Problem Understanding

Approach

Submit Your Answer

Splunk Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions