Cumulative sum in SQL using window function

Question

QTY	STOCK	RNK	ID KEY	CUM SUM
40	35	1	1	35
20	35	2	1	0
15	35	3	1	0
58	35	4	1	0
18	35	5	1	0
40	35	1	2	35
20	35	2	2	0
15	35	3	2	0

CUM SUM should be MIN(QTY, STOCK-SUM(all rows in cumsum before the current row)) for every other row and for 1st row it should be MIN(QTY, STOCK-SUM(0))=> MIN(QTY,STOCK)

QTY	STOCK	RNK	ID KEY	CUM SUM
40	35	1	1	5
20	35	2	1	-10
15	35	3	1	-30
58	35	4	1	-7
18	35	5	1	-24
40	35	1	2	5
20	35	2	2	-10
15	35	3	2	-30

After, I tried I am getting the above output

SELECT sum(qty-stock) over (
         partition by ID KEY
         ORDER BY rnk 
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) as CUM SUM
FROM TABLE

Need to get correct cumsum value using a window function in the existing table

Answer 1

You may use a rolling SUM() here, using SUM() as an analytic function:

SELECT *, SUM(QTY - STOCK) OVER (PARTITION BY ID_KEY ORDER BY RNK) AS CUM_SUM
FROM yourTable
ORDER BY ID_KEY, RNK;