Next Business Date Column

Question

I have a dataset that looks like this.

Where business_day indicates whether the transaction_created_date is a business day or not. I'm trying to sum the line_amount so that values that occurred over the holiday or weekend gets added to the next business day to look something like this:

Essentially, if I can capture the next business day where business_day = 0 then I can just do a sum over partition.

Answer 1

WITH
cte AS (
    SELECT *, SUM(business_day) OVER (ORDER BY transaction_created_date DESC) num
    FROM sourcetable
)
SELECT *, SUM(line_amount) OVER (PARTITION BY num) business_day_line_amount
FROM cte;