Redshift Postgresql Start row_number after a condition is met

Question

I'm trying to start a row number partitioned by cashaccountid, ordered by cashentrycreatedat that starts when a particular condition is met.

In this image, we only have one cashaccountid (to make things easier) and it's sorted by cashentrycreatedat. The row_number is starting the first time transactiontype = 'CASH DEPOSIT' and fundingtype = 'PURCHASE'. I used the query below to create the rn_after_first_sell_order, but what I really need is a genuine row_number once we have the first occurrence of transactiontype = 'CASH DEPOSIT' and fundingtype = 'PURCHASE'.

    sum(
    case
        when transactiontype = 'CASH DEPOSIT' and cashtransaction.type = 'PURCHASE' then 1
    end
) over (partition by cashentry.cashaccountid order by cashentry.createdat rows between unbounded preceding and current row) as rn_after_first_sell_order

Would anyone know how to obtain this result? I don't mind if everything before the first occurrence is 0.

score 0 · Answer 1 · answered Jul 06 '23 at 20:26

You can use a conditional window max to flag the rows at or after the event you expect, then enumerate the records of that group:

select c.*,
    case when has_started = 1
        then row_number() over(
            partition by cashaccountid, has_started
            order by createdat 
            rows between unbounded preceding and current row
        )
    end rn
from (
    select c.*,
        max(case when transactiontype = 'CASH DEPOSIT' and fundingtype = 'PURCHASE' then 1 else 0 end) 
            over(
                partition by cashaccountid 
                order by createdat 
                rows between unbounded preceding and current row
            ) has_started
    from cashentry c
) c

Redshift Postgresql Start row_number after a condition is met

1 Answers1