Calculate rolling 7-day average per product

Question

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

Codemia · Accepted Answer

The task is to compute a rolling 7-day average of a specific product usage per user. The data involved would typically include user identifiers, product identifiers, usage metrics (like sales or clicks), and timestamps. The output must display the user, product, and the calculated 7-day average usage, accommodating for any null values in the usage data and handling duplicate entries appropriately.

1. **Identify the Source Data**: Start with the relevant table(s) that contain user, product, timestamp, and usage metrics.  
2. **Clean the Data**: Remove duplicates and handle null values in the usage column.  
3. **Create a Time Series**: Use a Common Table Expression (CTE) to select the necessary columns and filter out nulls.  
4. **Calculate Rolling Average**: Apply a window function to compute the 7-day rolling average grouped by user and product.  
5. **Output the Results**: Select the user, product, and the calculated average, ensuring the results are ordered by user and timestamp.

```sql
WITH CleanedData AS (
    SELECT  
        user_id,  
        product_id,  
        usage,  
        date,  
        ROW_NUMBER() OVER (PARTITION BY user_id, product_id, date ORDER BY date) AS rn  
    FROM usage_table  
    WHERE usage IS NOT NULL  
),
Deduplicated AS (
    SELECT user_id, product_id, date, usage  
    FROM CleanedData  
    WHERE rn = 1  
)
SELECT  
    user_id,  
    product_id,  
    AVG(usage) OVER (PARTITION BY user_id, product_id ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day_avg  
FROM Deduplicated  
ORDER BY user_id, product_id, date;
```

To optimize the query for large datasets:  
1. **Indexing**: Create indexes on the `user_id`, `product_id`, and `date` columns to speed up filtering and ordering operations.  
2. **Partitioning**: If the dataset is extremely large, consider partitioning the table by date or product to improve performance on date-based queries.  
3. **Materialized Views**: If the rolling average needs to be computed frequently, consider creating a materialized view that pre-calculates these averages.

1. **NULL Values**: Ensure that all null usage values are filtered out in the CTE to prevent them from affecting the rolling average.  
2. **Duplicates**: The use of ROW_NUMBER ensures that duplicates are handled by keeping only one instance of each user-product-date combination.  
3. **Sparse Data**: If a user has no activity in the past 7 days, the rolling average might return NULL; handle this in application logic if necessary.  
4. **Time Gaps**: If there are large gaps in the date range for a user, ensure that the rolling average calculation respects the 7-day window correctly, potentially resulting in fewer data points contributing to the average.

Calculate rolling 7-day average per product

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