What is your process for determining the rolling average of a specific metric over a certain length of time?

Question

Anonymous · Accepted Answer

SELECT 
    date,
    metric_value,
    ROUND(AVG(metric_value) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ), 2) AS rolling_avg_7d
FROM 
    tbl
ORDER BY 
    date;

Anonymous · Answer

Assuming that the table is called product_sales which has data for product_id,
product_name, sales_amount and order_date . The data will be available for a
year and we want to do a rolling average on a monthly level.

Here , i will show how to do the rolling 28 days average of a product

WITH cte_order_data as (

SELECT cast(order_date as DATE) as date,

product_name,

SUM(sales_amount) as sales_amount

FROM product_sales

GROUP BY 1,2)

SELECT date,

product_name,

AVG(sales_amount) OVER (

ORDER BY date, product_name

ROWS BETWEEN 28 PRECEDING AND CURRENT ROW

) AS rolling_28d_avg

FROM cte_order_data

Anonymous · Answer

select

avg(metric column) over (order by date column rows between n preceding and
current row) as [rolling average]

from table

Anonymous · Answer

use aggregate function to compute rolling avg ex: ROUND(AVG(tweet_count) OVER (

PARTITION BY user_id

ORDER BY tweet_date

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

),2) AS rolling_avg_3d

What is your process for determining the rolling average of a specific metric over a certain length of time?

Practice this question with AI

Go Premium

Community Answers

Unlock Full Access

Unlock Full Access

Unlock Full Access

Unlock Full Access

Practice More Questions