Write a query to find rolling 7-day average from orders

Question

Write a SQL query to calculate rolling 7-day average from the orders table, considering nulls and duplicates.

Codemia · Accepted Answer

To solve the problem, we need to extract data from the 'orders' table, which contains information about individual orders placed by customers. The goal is to calculate the rolling 7-day average of the total order amounts. We must consider NULL values (which should not be counted in the average) and handle duplicates (if any exist). The final output should include the date and the corresponding 7-day rolling average.

1. **Identify Relevant Columns**: Determine which columns in the 'orders' table are necessary for the calculation, primarily focusing on the order date and order amount. 
2. **Aggregate Daily Totals**: Use a Common Table Expression (CTE) to aggregate the total order amounts per day, ensuring to handle NULLs by excluding them from the sum. 
3. **Calculate Rolling Average**: Use a window function to calculate the rolling 7-day average based on the daily totals computed in the CTE. 
4. **Filter Results**: Ensure that the final query filters out any days where no orders were placed.

```sql
WITH DailyTotals AS (
    SELECT 
        order_date, 
        SUM(order_amount) AS total_amount
    FROM orders
    WHERE order_amount IS NOT NULL
    GROUP BY order_date
)
SELECT 
    order_date,
    AVG(total_amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7_day_avg
FROM DailyTotals
ORDER BY order_date;
```

To optimize this query for large datasets, consider the following strategies: 
1. **Indexing**: Create an index on the 'order_date' column to speed up the aggregation and window function calculations. 
2. **Partitioning**: If the dataset is extremely large, partition the orders table by date or another relevant dimension to further enhance performance. 
3. **Materialized Views**: If this rolling average is frequently queried, consider using a materialized view to precompute and store the results, refreshing it at regular intervals.

1. **NULL Values**: We already filter out NULL order amounts in the CTE, but we should also ensure that the 'order_date' itself is not NULL. 
2. **Duplicates**: If there are duplicate entries for the same order on the same date, they will be summed in the aggregation. This may skew results unless the duplicates are expected and acceptable. 
3. **Date Gaps**: If there are days with no orders, the rolling average might not show values for those dates; consider how to handle such gaps (e.g., filling with the last known average).

Write a query to find rolling 7-day average from orders

Datadog

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

Datadog 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