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

in order to calculate rolling average, I would first understand the moving
window timeline, is it a week , a month , a quarter.
Then after having that understanding I would use the window function - Average
over, with rows between 6 preceeding or current row. But I would also like to
keep an account of the time line where say we have 0 revenue on a consequtive
day. Would need to have insight on how we are recording those days and if not
that use a time series function to add those days in the calculation window by
addding revenue as 0 using Coalesce and case method.

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

Unlock Full Access

Practice More Questions