I’ve read a lot of posts with similar questions but I’m hoping to accomplish this with no inner join. Using redshift with dbt so I am able to utilize jinja if that’s needed for a solution.

I have data that looks like this:

I need to sum up the value or use the cumulative_value based on the new_rank; if rank < new_rank, then sum up the value of previous rank records and add the new_rank_value

I would like the output to look like this, depicted by output field:
| rank | value | cumulative_value | new_rank | new_rank_value | output
| ——– | ——– | ——– | ——– | ——– | ——– |
| 1 | 10 | 10 | 1 | 1 | 11 |
| 2 | 11 | 21 | 2 | 2 | 23 |
| 3 | 12 | 33 | 3 | 3 | 36 |
| 4 | 13 | 46 | 3 | 4 | 37 |
| 5 | 14 | 60 | 3 | 5 | 38 |
| 6 | 15 | 75 | 5 | 6 | 66 |
| 7 | 16 | 91 | 5 | 7 | 67 |
| 8 | 17 | 108 | 8 | 8 | 116 |
| 9 | 18 | 126 | 9 | 9 | 135 |

If we look at:

• row 1: new_rank = rank, and the output is value + new_rank_value
• row 3: new_rank = rank, and the output is the current and previous value + new_rank_value
• row 4: new_rank = rank from a previous record, and the output is the current and previous value (from that previous record and prior) + new_rank_value

I’ve been able to accomplish this using inner joins, but it slows down my query and it can’t be slower than it already is (report’s for the company). Hoping someone can help me out do this with window functions/some other SQL that I haven’t been able to think of yet.

I’ve used various window functions in combination with row clauses, but haven’t had any luck

edit: updating table