Thanks for providing a complete test case with your desired output; it makes it so much easier for us to help you!
Anyway, on to the question. Restating your problem, if I've understood correctly you want:
The largest txn_id value less than the current for the other transaction type. Split this by customer.
It's tricky to do this with analytics. You can use partition by to divide things by type, but you want the other type. So I'm going to use the model clause for this.
First you want to group these by customer_id, so this goes in the partition by clause.
Next we need to decide how you're going to identify cells within the model. So we need a unique identifier for each row. A simple row_number(), ordered by txn_id does this. But we need a bit more. We want to find values by type and swap these over. So let's also put trans_type in the dimension by. While doing so, we want to merge CREDIT CARD and CASH into a single category (purchase). Let's use decode to do this.
So our dimension by clause is:
dimension by (
decode(trans_type, 'REDEMPTION', 'REDEMPTION', 'PURCHASE') tp,
row_number() over (partition by customer_id order by txn_id) rn
)
We're returning all the other columns, plus the one we're generating, lag_txn_id. So this plus the remaining table columns go in the measures clause:
measures ( trans_type, txn_id, 0 lag_txn_id )
So how do we get the values for lag_txn_id?
For any purchase, it's the maximum redemption so far. So the assignment target is:
lag_txn_id['PURCHASE', any] = <expression>
But what goes in the expression?
Well, it's the maximum redemption id so far:
max(txn_id)['REDEMPTION', rn <= cv()]
Whoa, what's going on there?
First, 'REDEMPTION' ensures we're only looking at redemption rows. Then cv() returns the current value of the dimension rn. So rn <= cv() includes all the rows where the assigned rn is less than the current. i.e. all rows so far (ordered by txn_id). The max simply returns the greatest of these.
For redemptions, simply swap purchase and redemption over.
Put it together and you have:
select *
from transactions t
model
partition by ( customer_id )
dimension by (
decode(trans_type, 'REDEMPTION', 'REDEMPTION', 'PURCHASE') tp,
row_number() over (partition by customer_id order by txn_id) rn
)
measures ( trans_type, txn_id, 0 lag_txn_id )
rules (
lag_txn_id['PURCHASE', any] = max(txn_id)['REDEMPTION', rn <= cv()],
lag_txn_id['REDEMPTION', any] = max(txn_id)['PURCHASE', rn <= cv()]
)
order by txn_id;
CUSTOMER_ID TP RN TRANS_TYPE TXN_ID LAG_TXN_ID
----------- ---------- ---------- --------------- ---------- ----------
444 PURCHASE 1 CREDIT_CARD 1
444 REDEMPTION 2 REDEMPTION 2 1
444 REDEMPTION 3 REDEMPTION 3 1
444 PURCHASE 4 CASH 4 3
444 REDEMPTION 5 REDEMPTION 5 4
999 PURCHASE 1 CASH 6
999 PURCHASE 2 CREDIT_CARD 7
999 REDEMPTION 3 REDEMPTION 8 7
999 REDEMPTION 4 REDEMPTION 9 7
999 REDEMPTION 5 REDEMPTION 10 7
10 rows selected