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;

  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.