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
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;