Could you describe how you calculate the rolling average for a metric over a particular time length?

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

Could you describe how you calculate the rolling average for a metric over a particular time length?

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