This example finds sequences of increasing balances. This implies that we reset whenever the current balance is less than or equal to the preceding balance.
SELECT account_key, month, balance, ROW_NUMBER() over (PARTITION BY account_key ORDER BY month RESET WHEN balance /* current row balance */ <= SUM(balance) over (PARTITION BY account_key ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) /* prev row */ ) - 1 /* to get the count started at 0 */ as balance_increase FROM accounts;
The possible results of the preceding SELECT appear in the following table:
account_key month balance balance_increase ----------- ----- ------- ---------------- 1 1 60 0 1 2 99 1 1 3 94 0 1 4 90 0 1 5 80 0 1 6 88 1 1 7 90 2 1 8 92 3 1 9 10 0 1 10 60 1 1 11 80 2 1 12 10 0