[SOLVED] Preceding rows sum and subtraction using SQL

Preceding rows sum and subtraction using SQL

I have the following dataset which I'm trying to create SQL to generate the FEE column.

CUSTOMER	YEAR	MONTH	REVENUE	FEE	NOTES
CUSTOMER A	FY24	1	0	0
CUSTOMER A	FY24	2	0	0
CUSTOMER A	FY24	3	0	0
CUSTOMER A	FY24	4	0	0
CUSTOMER A	FY24	5	0	0
CUSTOMER A	FY24	6	0	0
CUSTOMER A	FY24	7	5000	5000	FEE = REVENUE - SUM(ALL PRECEDING ROWS FOR FEES COLUMN FOR CUSTOMER AND YEAR) or 0
CUSTOMER A	FY24	8	0	0
CUSTOMER A	FY24	9	7000	2000	FEE = REVENUE - SUM(ALL PRECEDING ROWS FOR FEES COLUMN FOR CUSTOMER AND YEAR) or 5000
CUSTOMER A	FY24	10	150000	143000	FEE = REVENUE - SUM(ALL PRECEDING ROWS FOR FEES COLUMN FOR CUSTOMER AND YEAR) or 7000
CUSTOMER A	FY24	11	0	0
CUSTOMER A	FY24	12	0	0

I have tried a combination of window analytical functions however I can't seem to get to end result which is detailed above. Can anyone help with what SQL statement I can use to calculate the FEES column?

Thank you

Solution

There are two kind of rows:

those with a revenue = zero where you want a fee of zero
those with a revenue != zero where you want a calculation

You can use two queries for these, glueing the results with UNION ALL.

The calculation involves a window function as you already know. That window function is LAG, because you want the fee to be "this revenue minus last revenue".

SELECT customer, year, month, revenue, 0 as fee
FROM mytable
WHERE revenue = 0
UNION ALL
SELECT
  customer, year, month, revenue,
  revenue - COALESCE(LAG(revenue) OVER (PARTITION BY customer ORDER BY year, month), 0) AS fee
FROM mytable
WHERE revenue <> 0
ORDER BY customer, year, month;

If you really want to start each year anew, then you'd make this: PARTITION BY customer, year ORDER BY month.