SQL MOVING TOTALS

In Economics and Business, moving totals is a useful metric to track to understand the trends of performance on a continuous scale. Analyzing totals sales at the weekly level, monthly, quarterly or semi-annually are standard practices in the business world, therefore, computing these metrics directly in a query is very useful.

SYNTAX for Moving Aggregates

A useful and important note to make is the syntax that defines the window.

ROWS BETWEEN 6 PRECEDING AND CURRENT ROW

MOVING TOTALS IN Action

Let take an example where we calculation the moving total, not cumulative total, of the value of orders from our orders dataset. Specifically, we look at a 7 day moving total

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

  row_num | order_date | total_amount | mv_total
----------+------------+--------------+----------
        1 | 2022-02-10 |      1249.98 |
        2 | 2022-02-15 |      2499.99 |
        3 | 2022-02-20 |       599.99 |
        4 | 2022-02-25 |      1799.99 |
        5 | 2022-03-05 |       349.99 |
        6 | 2022-03-10 |       999.99 |
        7 | 2022-03-15 |       249.99 |  7749.92
        8 | 2022-03-20 |       799.99 |  7299.93
        9 | 2022-03-25 |      1299.99 |  6099.93
       10 | 2022-04-01 |       499.99 |  5999.93
       11 | 2022-04-05 |      2999.98 |  7199.92
       12 | 2022-04-10 |       149.99 |  6999.92
       13 | 2022-04-15 |       399.99 |  6399.92
       14 | 2022-04-20 |       669.99 |  6819.92
       15 | 2022-04-25 |        99.99 |  6119.92
       16 | 2022-05-02 |      3899.99 |  8719.92
       17 | 2022-05-07 |       149.99 |  8369.92
       18 | 2022-05-12 |       449.99 |  5819.93
       19 | 2022-05-17 |        79.99 |  5749.93
       20 | 2022-05-22 |        89.99 |  5439.93
(20 rows)

The implementation of moving totals for this example is for a 7 day moving totals. To illustrate it further, I use a case method to set the first 6 values to NULL.