MOVING AVERAGES

Moving averages are useful metrics to gauge the trend of performance of a business and economic metrics. Some example can be average weekly or monthly sales. If you are using SQL in a business context or broadly in analytics, you will likely need to compute moving averages at some point.

In the query below, I demonstrate how to compute Moving Average using window function syntax.

SELECT
    row_number() OVER() as row_num,
    order_date,
    total_amount,
    AVG(total_amount) OVER(ORDER BY order_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS mv_avg,
    CASE WHEN row_number() over() < 7 then NULL
        ELSE AVG(total_amount) OVER(ORDER BY order_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
    END AS mv_avg_offset
FROM orders
WHERE order_date IS NOT NULL
LIMIT 20;

OUTPUT row_num | order_date | total_amount | mv_avg | mv_avg_offset ----------+------------+--------------+-----------------------+----------------------- 1 | 2022-02-10 | 1249.98 | 1249.9800000000000000 | 2 | 2022-02-15 | 2499.99 | 1874.9850000000000000 | 3 | 2022-02-20 | 599.99 | 1449.9866666666666667 | 4 | 2022-02-25 | 1799.99 | 1537.4875000000000000 | 5 | 2022-03-05 | 349.99 | 1299.9880000000000000 | 6 | 2022-03-10 | 999.99 | 1249.9883333333333333 | 7 | 2022-03-15 | 249.99 | 1107.1314285714285714 | 1107.1314285714285714 8 | 2022-03-20 | 799.99 | 1042.8471428571428571 | 1042.8471428571428571 9 | 2022-03-25 | 1299.99 | 871.4185714285714286 | 871.4185714285714286 10 | 2022-04-01 | 499.99 | 857.1328571428571429 | 857.1328571428571429 11 | 2022-04-05 | 2999.98 | 1028.5600000000000000 | 1028.5600000000000000 12 | 2022-04-10 | 149.99 | 999.9885714285714286 | 999.9885714285714286 13 | 2022-04-15 | 399.99 | 914.2742857142857143 | 914.2742857142857143 14 | 2022-04-20 | 669.99 | 974.2742857142857143 | 974.2742857142857143 15 | 2022-04-25 | 99.99 | 874.2742857142857143 | 874.2742857142857143 16 | 2022-05-02 | 3899.99 | 1245.7028571428571429 | 1245.7028571428571429 17 | 2022-05-07 | 149.99 | 1195.7028571428571429 | 1195.7028571428571429 18 | 2022-05-12 | 449.99 | 831.4185714285714286 | 831.4185714285714286 19 | 2022-05-17 | 79.99 | 821.4185714285714286 | 821.4185714285714286 20 | 2022-05-22 | 89.99 | 777.1328571428571429 | 777.1328571428571429 (20 rows)

As we have seen with Moving Totals, we can use the ROWS syntax to specify our window for moving averages to get the moving average window we need. A useful detail to remember is that moving averages will be computed as long as the number of rows preceding the current row exists. That is, if you are computing the moving average for a 7-day window, it is only the 7th row and beyond that will have accurate 7-day averages. The prior rows will have averages by the window as the number of rows before them.