sqlgroup-bysql-view

SQL View (or query) to show cumulative sum over time


Let's assume I have the following data in an SQL table called transactions with primary keys tx_date, location, item:

tx_date       location    item         quantity    fee
2024-01-01    US          ITEM.123         1000     20
2024-02-04    EMEA        ITEM.456789       600     12
2024-03-09    US          ITEM.123         -750    -30
2024-04-12    EMEA        ITEM.456789       700     25
2024-05-16    EMEA        ITEM.456789      -800    -35

How can I create a view that shows the net_quantity and net_fee of each (location, item) pair on each tx_date? Effectively, the cumulative sum over time:

tx_date       location    item        net_quantity   net_fee
2024-01-01    US          ITEM.123            1000        20
2024-02-04    EMEA        ITEM.456789          600        12
2024-03-09    US          ITEM.123             250       -10
2024-04-12    EMEA        ITEM.456789         1300        37
2024-05-16    EMEA        ITEM.456789          500         2

Solution

  • WITH CumulativeSums AS (
    SELECT
        tx_date,
        location,
        item,
        quantity,
        fee,
        SUM(quantity) OVER (PARTITION BY location, item ORDER BY tx_date) AS net_quantity,
        SUM(fee) OVER (PARTITION BY location, item ORDER BY tx_date) AS net_fee
    FROM
        transactions
    )
    SELECT
        tx_date,
        location,
        item,
        net_quantity,
        net_fee
    FROM
        CumulativeSums
    ORDER BY
        location,
        item,
        tx_date;
    

    enter image description here