SQL Rolling Average Calculation using Window Functions

Query Using Window Functions to Calculate Rolling Average

-- Query to calculate rolling average using window functions
SELECT
    date_column,
    value_column,
    AVG(value_column) OVER (ORDER BY date_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_avg
FROM
    your_table_name
ORDER BY
    date_column;

Explanation:

• The query calculates a rolling average of a specific column (value_column) over a specified time frame.
• date_column is the column representing the dates.
• value_column is the column for which the rolling average needs to be calculated.
• OVER (ORDER BY date_column ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) defines the window frame for the average calculation. In this case, it calculates the average of the current row and the two preceding rows.
• The result includes date_column, original value_column, and the calculated rolling_avg.
• your_table_name should be replaced with the actual name of the table in your database.
• The result set is ordered by date_column to show the data in chronological order.