SQL Redshift conditional running sum based on the running sum result

Question

I have a table with different invoice values (column "invoice") aggregated by month (column "date") and partner (column "partner"). I need to have as output a running sum that starts when my invoice value is negative and continues until the running sum becomes positive. Below is the representation of what would be the result I need:

In the date 2022-05-01, the invoice value is 100, so it should not be considered in the running sum. In the date 2022-06-01, the invoice value is negative (-250) so the running sum should start until it turns into a positive value (which happens in the date 2022-09-01). The same logic should happen continuously: In the date 2022-11-01 the running sum starts again and goes until 2023-01-01 when it becomes positive again.

What SQL query could get me the output I need?

I tried to perform a partition sum based on the partner and ordered by date when the invoice value is negative, however the running sum starts and stop in the negative values and don't consider the positive values in the sequence.

 Select date ,partner ,case when invoice > 0 then 1 else 0 end as index ,sum(invoice) over (partition by partner,index order by date) from table t

Please provide your data as tabular text rather than as a picture, that we can’t copy paste. — GMB
– GMB, Commented Jan 11, 2023 at 21:05

GMB · Accepted Answer · 2023-01-11 21:33:18Z

The logic you describe requires iterating over the dataset. Window functions do not provide this feature: in SQL, we would use a recursive query to address this problem.

Redshift recently added support for recursive CTEs: yay! Consider:

with recursive data (date, partner, invoice, rn) as ( select date, partner, invoice, row_number() over(partition by partner order by date) rn from mytable ), cte (date, partner, invoice, rn, rsum) as ( select date, partner, invoice, rn, invoice from data where rn = 1 union all select d.date, d.partner, d.invoice, d.rn, case when c.rsum >= 0 and c.rn > 1 then d.invoice else c.rsum + d.invoice end from cte c inner join data d on d.partner = c.partner and d.rn = c.rn + 1 ) select * from cte order by partner, date

Collectives™ on Stack Overflow

SQL Redshift conditional running sum based on the running sum result

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related