Calculate rolling 7-day average per region

Question

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

Codemia · Accepted Answer

To compute the rolling 7-day average per region, we need to work with a dataset that likely contains user activity records, including fields such as user ID, region, date, and an activity metric (e.g., number of sessions, transactions, or similar). The goal is to calculate the average of this activity metric over the last 7 days for each user, grouped by their respective regions. Handling data quality issues like null values and duplicate entries is crucial to ensure accurate calculations.

1. **Data Selection**: Start by selecting the relevant fields from the user activity dataset, ensuring to include user ID, region, date, and the activity metric.
2. **Handling Nulls**: Use a WHERE clause to filter out records with null activity metrics, as they won't contribute to the rolling average.
3. **Window Function**: Utilize a window function (e.g., `AVG() OVER (...)`) to compute the 7-day rolling average. The window will be defined using the `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` clause.
4. **Group By Region and User**: Group the results by user and region to ensure that the averages are calculated correctly for each segment.
5. **Sorting Data**: Ensure the data is sorted by date within each user-region group to maintain the correct order for the rolling average.

```sql
WITH ActivityData AS (
    SELECT user_id, region, date, activity_metric
    FROM user_activity
    WHERE activity_metric IS NOT NULL
),
RollingAverage AS (
    SELECT user_id,
           region,
           date,
           AVG(activity_metric) OVER (PARTITION BY user_id, region ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_avg
    FROM ActivityData
)
SELECT user_id,
       region,
       date,
       rolling_avg
FROM RollingAverage
ORDER BY user_id, region, date;
```

For large datasets, consider the following optimizations:
1. **Indexing**: Create indexes on columns used in the `WHERE`, `PARTITION BY`, and `ORDER BY` clauses, such as `date`, `user_id`, and `region`. This will speed up data retrieval and the execution of window functions.
2. **Materialized Views**: If this rolling average needs to be computed frequently, consider creating a materialized view that stores precomputed results, which can be refreshed periodically.
3. **Batch Processing**: If working with a real-time data stream, consider processing data in batches to avoid overwhelming the database during peak loads.

1. **NULL Values**: The query already filters out null activity metrics; however, if the dataset might include null dates, additional filtering should be applied.
2. **Duplicates**: Ensure that the dataset does not contain duplicate records for the same user, date, and region combination. If duplicates exist, consider using `ROW_NUMBER()` to rank and select distinct records before calculating the rolling average.
3. **Sparse Data**: If there are users without activity for several days, the rolling average may not be computed for those days. Make sure to handle such cases by either filling in missing dates with zeros or using an appropriate technique to represent inactivity.

Calculate rolling 7-day average per region

Cloudflare

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

Cloudflare 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