In Athena, I want to calculate a rolling sum, over a window, that can't go below 0. For example, if summing up a column that has values (1, 2, -1, -2, -1, -2, 1, 2) I should get (1, 3, 2, 0, 0, 0, 1, 3).

Without the floor constraint, it's easy - SUM(X) OVER (PARTITION BY some_group ORDER BY ordering_col). Or if we just wanted to clip all the values at 0 after doing an actual cumulative sum. But I cannot figure out how to use window functions to achieve my desired result in general, though I can make it work for some special cases.

Is this even possible?

1 Answer 1


don't know if you are still looking for a solution but I ran into a similar problem.

I solved that using reduce function after aggregating the appropriate window as array. https://stackoverflow.com/questions/70470329/interesting-problem-concerned-with-updating-a-value-in-presto-sql/70484801#70484801

I think with slight modification this approach will work.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.