Skip to Main Content
  • Questions
  • Tricky Analytics LAG and OFFSET Question

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Adam.

Asked: December 15, 2015 - 4:10 pm UTC

Last updated: December 19, 2015 - 4:23 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

Hi, Ask Tom Team,

The dataset is straightforward. Customers make purchases via CREDIT CARD or CASH and they can do REDEMPTIONs for a certain amount (not relevant to the issue at hand).

Here is sample final data, sample code below. Assume all transactions are in ascending chronological order per CUSTOMER_ID; I need SQL that will give the following result for LAG_TXN_ID per these rules:

For each CUSTOMER_ID:
For a REDEMPTION, I need to determine the most previous purchase, i.e., CREDIT CARD or CASH transaction id (TXN_ID);
For a purchase, I need to determine the most previous REDEMPTION transaction id.

CUSTOMER_ID TXN_ID TRANS_TYPE LAG_TXN_ID
444 1 CREDIT_CARD null
444 2 REDEMPTION 1
444 3 REDEMPTION 1
444 4 CASH 3
444 5 REDEMPTION 4
999 6 CASH null
999 7 CREDIT_CARD null
999 8 REDEMPTION 7
999 9 REDEMPTION 7
999 10 REDEMPTION 7


I've tried nested windows functions using row_number() as the LAG(..., OFFSET) value which work in simple cases, but not the above example.

Any and all suggestions are much appreciated.

Thanks!
Adam

create table transactions
(CUSTOMER_ID number,
TXN_ID number,
TRANS_TYPE varchar2(50))
/

-- insert sample data
insert into transactions values ( 444 , 1 , 'CREDIT_CARD');
insert into transactions values ( 444 , 2 , 'REDEMPTION');
insert into transactions values ( 444 , 3 , 'REDEMPTION');
insert into transactions values ( 444 , 4 , 'CASH');
insert into transactions values ( 444 , 5 , 'REDEMPTION');
insert into transactions values ( 999 , 6 , 'CASH');
insert into transactions values ( 999 , 7 , 'CREDIT_CARD');
insert into transactions values ( 999 , 8 , 'REDEMPTION');
insert into transactions values ( 999 , 9 , 'REDEMPTION');
insert into transactions values ( 999 , 10 , 'REDEMPTION');
commit;

select * from transactions;

and Chris said...

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

Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Well, Two solution

Rajeshwaran Jeyabal, December 16, 2015 - 2:55 pm UTC

Well, Two solution

With Analytics and MODEL.

rajesh@ORA10G> set feedback off
rajesh@ORA10G> drop table t purge;
rajesh@ORA10G> create table t(customer_id int,txn_id int,
  2     trans_type varchar2(15),lag_txn_id int);
rajesh@ORA10G> insert into t values(444,1,'CREDIT_CARD',null);
rajesh@ORA10G> insert into t values(444,2,'REDEMPTION',1);
rajesh@ORA10G> insert into t values(444,3,'REDEMPTION',1);
rajesh@ORA10G> insert into t values(444,4,'CASH',3);
rajesh@ORA10G> insert into t values(444,5,'REDEMPTION',4);
rajesh@ORA10G> insert into t values(999,6,'CASH',null);
rajesh@ORA10G> insert into t values(999,7,'CREDIT_CARD',null);
rajesh@ORA10G> insert into t values(999,8,'REDEMPTION',7);
rajesh@ORA10G> insert into t values(999,9,'REDEMPTION',7);
rajesh@ORA10G> insert into t values(999,10,'REDEMPTION',7);
rajesh@ORA10G> commit;
rajesh@ORA10G> set feedback on
rajesh@ORA10G>
rajesh@ORA10G> select customer_id,txn_id,trans_type,lag_txn_id,
  2    last_value(grp ignore nulls) over(partition by customer_id
  3        order by txn_id) as grp
  4  from (
  5  select t.*  ,
  6    case when trans_type = 'REDEMPTION' and
  7      lag(trans_type) over(partition by customer_id
  8              order by txn_id) in ('CASH','CREDIT_CARD') then
  9        lag(txn_id) over(partition by customer_id order by txn_id)
 10       when trans_type in ('CASH','CREDIT_CARD') and
 11        lag(trans_type) over(partition by customer_id order by txn_id ) ='REDEMPTION' then
 12        lag(txn_id) over(partition by customer_id order by txn_id)  end grp
 13  from t
 14      )
 15  order by customer_id,txn_id
 16  /

CUSTOMER_ID     TXN_ID TRANS_TYPE      LAG_TXN_ID        GRP
----------- ---------- --------------- ---------- ----------
        444          1 CREDIT_CARD
        444          2 REDEMPTION               1          1
        444          3 REDEMPTION               1          1
        444          4 CASH                     3          3
        444          5 REDEMPTION               4          4
        999          6 CASH
        999          7 CREDIT_CARD
        999          8 REDEMPTION               7          7
        999          9 REDEMPTION               7          7
        999         10 REDEMPTION               7          7

10 rows selected.

rajesh@ORA10G>
rajesh@ORA10G>
rajesh@ORA10G> select *
  2  from t
  3  model
  4     partition by (customer_id)
  5     dimension by (row_number() over(partition by customer_id
  6             order by txn_id) r)
  7     measures( trans_type, txn_id, lag_txn_id, cast(null as int)grp)
  8     rules
  9     ( grp[r>1] = case when trans_type[cv(r)]='REDEMPTION' and
 10             trans_type[cv(r)-1] in ('CASH','CREDIT_CARD') then
 11             txn_id[cv(r)-1]
 12             when trans_type[cv(r)] in ('CASH','CREDIT_CARD') and
 13                trans_type[cv(r)-1] ='REDEMPTION' then
 14                txn_id[cv(r)-1]
 15             else grp[cv(r)-1] end )
 16  order by customer_id,txn_id
 17  /

CUSTOMER_ID          R TRANS_TYPE          TXN_ID LAG_TXN_ID        GRP
----------- ---------- --------------- ---------- ---------- ----------
        444          1 CREDIT_CARD              1
        444          2 REDEMPTION               2          1          1
        444          3 REDEMPTION               3          1          1
        444          4 CASH                     4          3          3
        444          5 REDEMPTION               5          4          4
        999          1 CASH                     6
        999          2 CREDIT_CARD              7
        999          3 REDEMPTION               8          7          7
        999          4 REDEMPTION               9          7          7
        999          5 REDEMPTION              10          7          7

10 rows selected.

rajesh@ORA10G>

Adam Kahn, December 16, 2015 - 7:54 pm UTC

Chris, thanks so much for your elegant answer and swift response. Much appreciated.

One assumption which can't be made is to pick the max(txn_id) in the RULES clause; txn_id is a surrogate key in the warehouse, can't assume a higher value occurred later in time as data does get regenerated on occasion. Really I want the max(rn) per customer_id within the valid RULES/business logic of my original post.

Updated sample data DDL below which includes TRANS_DATE. I've done some Googling without luck to understand how to modify the RULES clause - my latest attempt with errors at the end of the code below:

Thanks again in advance.

create table transactions 
(CUSTOMER_ID number, 
 TRANS_DATE date,
TXN_ID number, 
TRANS_TYPE varchar2(50)) 
/ 

-- insert sample data 
insert into transactions values ( 444 , to_date('01-mar-2013'), 10 , 'CREDIT_CARD'); 
insert into transactions values ( 444 , to_date('01-aug-2014'), 2 , 'REDEMPTION'); 
insert into transactions values ( 444 , to_date('01-nov-2014'), 3 , 'REDEMPTION'); 
insert into transactions values ( 444 , to_date('01-oct-2015'), 11 , 'CASH'); 
insert into transactions values ( 444 , to_date('01-dec-2015'), 17 , 'REDEMPTION'); 
insert into transactions values ( 999 , to_date('01-jan-2014'), 50, 'CASH'); 
insert into transactions values ( 999 , to_date('01-feb-2014'), 40, 'CREDIT_CARD'); 
insert into transactions values ( 999 , to_date('01-jan-2015'), 20, 'REDEMPTION'); 
insert into transactions values ( 999 , to_date('01-jun-2015'), 21, 'REDEMPTION'); 
insert into transactions values ( 999 , to_date('01-dec-2015'), 22 , 'REDEMPTION'); 
commit; 

select * from transactions; 

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 trans_date) rn  
  )  
  measures ( trans_date, trans_type, txn_id, 0 lag_txn_id )  
  rules (  
    lag_txn_id['PURCHASE', any] = txn_id  ['REDEMPTION', max(rn) <= cv()],  
    lag_txn_id['REDEMPTION', any] = txn_id ['PURCHASE', max(rn) <= cv()]   
  )  
order by customer_id, trans_date
/



gives this error: ORA-32622: illegal multi-cell reference



Chris Saxon
December 17, 2015 - 9:43 am UTC

You're getting the error because Oracle is trying to assign multiple cells to the same value (or think it will).

The max needs to stay on the txn_id. You can use the keep dense_rank last function to get the value ordered by trans_date though:

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 trans_date) rn  
  )  
  measures ( trans_date, trans_type, txn_id, 0 lag_txn_id )  
  rules (  
    lag_txn_id['PURCHASE', any] = 
    max(txn_id) keep (dense_rank last order by trans_date) ['REDEMPTION', rn <= cv()],
    lag_txn_id['REDEMPTION', any] = 
    max(txn_id) keep (dense_rank last order by trans_date) ['PURCHASE', rn <= cv()]
  )  
order by customer_id, trans_date;

CUSTOMER_ID TP                 RN TRANS_DATE        TRANS_TYPE          TXN_ID LAG_TXN_ID
----------- ---------- ---------- ----------------- --------------- ---------- ----------
        444 PURCHASE            1 01-MAR-2013 00:00 CREDIT_CARD             10           
        444 REDEMPTION          2 01-AUG-2014 00:00 REDEMPTION               2         10
        444 REDEMPTION          3 01-NOV-2014 00:00 REDEMPTION               3         10
        444 PURCHASE            4 01-OCT-2015 00:00 CASH                    11          3
        444 REDEMPTION          5 01-DEC-2015 00:00 REDEMPTION              17         11
        999 PURCHASE            1 01-JAN-2014 00:00 CASH                    50           
        999 PURCHASE            2 01-FEB-2014 00:00 CREDIT_CARD             40           
        999 REDEMPTION          3 01-JAN-2015 00:00 REDEMPTION              20         40
        999 REDEMPTION          4 01-JUN-2015 00:00 REDEMPTION              21         40
        999 REDEMPTION          5 01-DEC-2015 00:00 REDEMPTION              22         40

 10 rows selected 



To: Adam Kahn

Rajeshwaran Jeyabal, December 17, 2015 - 5:44 am UTC

Adam - did you tried with my sql, i tried with your new data sets, i got this.check if that helps.

rajesh@ORA10G> select *
  2  from transactions
  3  model
  4    partition by (customer_id)
  5    dimension by (row_number() over(partition by customer_id
  6               order by txn_id) r)
  7    measures( trans_type, txn_id,  cast(null as int)grp)
  8    rules
  9    ( grp[r>1] = case when trans_type[cv(r)]='REDEMPTION' and
 10               trans_type[cv(r)-1] in ('CASH','CREDIT_CARD') then
 11               txn_id[cv(r)-1]
 12               when trans_type[cv(r)] in ('CASH','CREDIT_CARD') and
 13                      trans_type[cv(r)-1] ='REDEMPTION' then
 14                      txn_id[cv(r)-1]
 15               else grp[cv(r)-1] end )
 16  order by customer_id,txn_id
 17  /

CUSTOMER_ID          R TRANS_TYPE       TXN_ID        GRP
----------- ---------- ------------ ---------- ----------
        444          1 REDEMPTION            2
        444          2 REDEMPTION            3
        444          3 CREDIT_CARD          10          3
        444          4 CASH                 11          3
        444          5 REDEMPTION           17         11
        999          1 REDEMPTION           20
        999          2 REDEMPTION           21
        999          3 REDEMPTION           22
        999          4 CREDIT_CARD          40         22
        999          5 CASH                 50         22

10 rows selected.

rajesh@ORA10G>
rajesh@ORA10G> select customer_id,txn_id,trans_type,
  2  last_value(grp ignore nulls) over(partition by customer_id
  3     order by txn_id) as grp
  4  from (
  5     select t.*  ,
  6     case when trans_type = 'REDEMPTION' and
  7       lag(trans_type) over(partition by customer_id
  8                       order by txn_id) in ('CASH','CREDIT_CARD') then
  9             lag(txn_id) over(partition by customer_id order by txn_id)
 10        when trans_type in ('CASH','CREDIT_CARD') and
 11             lag(trans_type) over(partition by customer_id order by txn_id ) ='REDEMPTION' then
 12             lag(txn_id) over(partition by customer_id order by txn_id)  end grp
 13     from transactions t
 14    )
 15  order by customer_id,txn_id
 16  /

CUSTOMER_ID     TXN_ID TRANS_TYPE          GRP
----------- ---------- ------------ ----------
        444          2 REDEMPTION
        444          3 REDEMPTION
        444         10 CREDIT_CARD           3
        444         11 CASH                  3
        444         17 REDEMPTION           11
        999         20 REDEMPTION
        999         21 REDEMPTION
        999         22 REDEMPTION
        999         40 CREDIT_CARD          22
        999         50 CASH                 22

10 rows selected.

rajesh@ORA10G>

Connor McDonald
December 17, 2015 - 9:45 am UTC

He asked for the data ordered by trans_date. If you change the order bys to that instead of txn_id it looks like it'll work:

select *
from transactions
model
  partition by (customer_id)
  dimension by (row_number() over ( partition by customer_id order by trans_date ) r)
  measures( trans_type, txn_id, trans_date, cast(null as int)grp)
  rules ( 
   grp[r>1] = 
    case 
     when trans_type[cv(r)]='REDEMPTION' and
       trans_type[cv(r)-1] in ('CASH','CREDIT_CARD') then
          txn_id[cv(r)-1]
        when trans_type[cv(r)] in ('CASH','CREDIT_CARD') and
             trans_type[cv(r)-1] = 'REDEMPTION' then
          txn_id[cv(r)-1]
        else 
      grp[cv(r)-1] 
    end 
  )
order by customer_id,trans_date;

CUSTOMER_ID          R TRANS_TYPE          TXN_ID TRANS_DATE               GRP
----------- ---------- --------------- ---------- ----------------- ----------
        444          1 CREDIT_CARD             10 01-MAR-2013 00:00           
        444          2 REDEMPTION               2 01-AUG-2014 00:00         10
        444          3 REDEMPTION               3 01-NOV-2014 00:00         10
        444          4 CASH                    11 01-OCT-2015 00:00          3
        444          5 REDEMPTION              17 01-DEC-2015 00:00         11
        999          1 CASH                    50 01-JAN-2014 00:00           
        999          2 CREDIT_CARD             40 01-FEB-2014 00:00           
        999          3 REDEMPTION              20 01-JAN-2015 00:00         40
        999          4 REDEMPTION              21 01-JUN-2015 00:00         40
        999          5 REDEMPTION              22 01-DEC-2015 00:00         40

 10 rows selected 

thanks.

Rajeshwaran Jeyabal, December 17, 2015 - 12:05 pm UTC

Thanks. i messed up with "order by" instead of Txn_id it should be on Trans_date, the both queries provides the same data.

rajesh@ORA10G> select *
  2     from transactions
  3     model
  4       partition by (customer_id)
  5       dimension by (row_number() over(partition by customer_id
  6                  order by trans_date) r)
  7       measures( trans_type, txn_id,  cast(null as int)grp,trans_date)
  8       rules
  9       ( grp[r>1] order by r = case when trans_type[cv(r)]='REDEMPTION' and
 10                  trans_type[cv(r)-1] in ('CASH','CREDIT_CARD') then
 11                  txn_id[cv(r)-1]
 12                  when trans_type[cv(r)] in ('CASH','CREDIT_CARD') and
 13                         trans_type[cv(r)-1] ='REDEMPTION' then
 14                         txn_id[cv(r)-1]
 15                  else grp[cv(r)-1] end )
 16     order by customer_id,trans_date
 17  /

CUSTOMER_ID          R TRANS_TYPE       TXN_ID        GRP TRANS_DATE
----------- ---------- ------------ ---------- ---------- -----------
        444          1 CREDIT_CARD          10            01-MAR-2013
        444          2 REDEMPTION            2         10 01-AUG-2014
        444          3 REDEMPTION            3         10 01-NOV-2014
        444          4 CASH                 11          3 01-OCT-2015
        444          5 REDEMPTION           17         11 01-DEC-2015
        999          1 CASH                 50            01-JAN-2014
        999          2 CREDIT_CARD          40            01-FEB-2014
        999          3 REDEMPTION           20         40 01-JAN-2015
        999          4 REDEMPTION           21         40 01-JUN-2015
        999          5 REDEMPTION           22         40 01-DEC-2015

10 rows selected.

rajesh@ORA10G> select customer_id,trans_date,txn_id,trans_type,
  2    last_value(grp ignore nulls) over(partition by customer_id
  3       order by trans_date) as grp
  4    from (
  5       select t.*  ,
  6       case when trans_type = 'REDEMPTION' and
  7         lag(trans_type) over(partition by customer_id
  8                         order by trans_date) in ('CASH','CREDIT_CARD') then
  9               lag(txn_id) over(partition by customer_id order by trans_date)
 10          when trans_type in ('CASH','CREDIT_CARD') and
 11               lag(trans_type) over(partition by customer_id order by trans_date ) ='REDEMPTION' then
 12               lag(txn_id) over(partition by customer_id order by trans_date)  end grp
 13       from transactions t
 14      )
 15    order by customer_id,trans_date
 16  /

CUSTOMER_ID TRANS_DATE      TXN_ID TRANS_TYPE          GRP
----------- ----------- ---------- ------------ ----------
        444 01-MAR-2013         10 CREDIT_CARD
        444 01-AUG-2014          2 REDEMPTION           10
        444 01-NOV-2014          3 REDEMPTION           10
        444 01-OCT-2015         11 CASH                  3
        444 01-DEC-2015         17 REDEMPTION           11
        999 01-JAN-2014         50 CASH
        999 01-FEB-2014         40 CREDIT_CARD
        999 01-JAN-2015         20 REDEMPTION           40
        999 01-JUN-2015         21 REDEMPTION           40
        999 01-DEC-2015         22 REDEMPTION           40

10 rows selected.

rajesh@ORA10G>

on 12c - Pattern matching

Rajeshwaran Jeyabal, December 17, 2015 - 1:19 pm UTC

Chris / Connor,

I am trying to use 12c pattern matching to solve this. But ended up with this.

I was expecting grp3=10 for the second row in the result set, which is perfect. but why don't i see grp3=40 for the Eighth row in this result ? please help me to understand - what am i missing here.

rajesh@ORA12C> select *
  2  from transactions
  3  match_recognize(
  4  partition by customer_id
  5  order by trans_date
  6  measures
  7    case
  8      when prev(trans_type) in ('CASH','CREDIT_CARD') and
  9          trans_type = 'REDEMPTION' then prev(txn_id)
 10      when prev(trans_type) = trans_type then first(txn_id)
 11      when prev(trans_type) = 'REDEMPTION' and
 12          trans_type in ('CASH','CREDIT_CARD') then prev(txn_id)
 13          end grp ,
 14      prev(trans_type) as prev_trans_type,
 15      trans_type as curr_trans_type,
 16      case when prev(trans_type)  = 'CREDIT_CARD' and
 17          trans_type = 'REDEMPTION' then prev(txn_id) end grp3
 18  all rows per match with unmatched rows
 19  after match skip past last row
 20  pattern( cash redemption+ | credit redemption+ | redemption cash+ | redemption credit+ )
 21  define
 22    cash as cash.trans_type ='CASH' ,
 23    credit as credit.trans_type='CREDIT_CARD',
 24    redemption as redemption.trans_type='REDEMPTION' )
 25  /

CUSTOMER_ID TRANS_DATE         GRP PREV_TRANS_TY CURR_TRANS_TY       GRP3     TXN_ID TRANS_TYPE
----------- ----------- ---------- ------------- ------------- ---------- ---------- ------------
        444 01-MAR-2013                          CREDIT_CARD                      10 CREDIT_CARD
        444 01-AUG-2014         10 CREDIT_CARD   REDEMPTION            10          2 REDEMPTION
        444 01-NOV-2014         10 REDEMPTION    REDEMPTION                        3 REDEMPTION
        444 01-OCT-2015          3 REDEMPTION    CASH                             11 CASH
        444 01-DEC-2015         11 CASH          REDEMPTION                       17 REDEMPTION
        999 01-JAN-2014                                                           50
        999 01-FEB-2014            CASH          CREDIT_CARD                      40 CREDIT_CARD
        999 01-JAN-2015            CREDIT_CARD   REDEMPTION                       20 REDEMPTION
        999 01-JUN-2015            REDEMPTION    REDEMPTION                       21 REDEMPTION
        999 01-DEC-2015            REDEMPTION    REDEMPTION                       22 REDEMPTION

10 rows selected.

rajesh@ORA12C>

Command performance

Stew Ashton, December 18, 2015 - 6:18 pm UTC

Rajeshwaran asked me to contribute a "pattern matching" solution, so with my apologies:
select * from transactions
match_recognize(
  partition by customer_id order by trans_date
  measures decode(classifier(), 'P', r.txn_id, p.txn_id) lag_txn_id
  all rows per match
  pattern( (p|r)+ )
  define p as trans_type in ('CREDIT_CARD', 'CASH'),
    r as trans_type = 'REDEMPTION'    
);

The PATTERN clause says every row is either P (purchase) or R (redemption). In the MEASURES clause, by default P refers to the most recent P row and R refers to the most recent R row. CLASSIFIER() tells me whether the current row is P or R.

After doing my bit, I started to wonder whether analytics would do the trick. Here's my attempt:
select t.*,
  case when trans_type = 'REDEMPTION' then
      last_value(case when trans_type != 'REDEMPTION' then txn_id end) ignore nulls
        over(partition by customer_id order by trans_date)
    else
      last_value(case when trans_type  = 'REDEMPTION' then txn_id end) ignore nulls
        over(partition by customer_id order by trans_date)
  end lag_txn_id
from transactions t;

Connor McDonald
December 19, 2015 - 4:23 am UTC

THanks for stopping by Stew.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.