sqlsumsnowflake-cloud-data-platformwindow-functionssnowflake-schema

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:

    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.