How do I find the next status and next date for a subscriber and offer?

my table:

create table myTable ( user_id,offer_id,status,status_date) as
 select   1,offer_1,Active,01/01/2021 from dual union all
 select   1, offer_1,Deactive,01/01/2022  from dual union all
 select   1,offer_2,Active,02/01/2022  from dual 

expected table:

CodePudding user response：

What do these dates represent? What is 02/01/2022? 2nd of January, or 1st of February?

Anyway, outer join might help.

Setting date format (so that you'd know what is what):

SQL> alter session set nls_date_format = 'mm/dd/yyyy';

Session altered.

Sample data:

SQL> select * from mytable;

   USER_ID OFFER_I STATUS   STATUS_DAT
---------- ------- -------- ----------
         1 offer_1 Active   01/01/2021
         1 offer_1 Deactive 01/01/2022
         1 offer_2 Active   02/01/2022

Query:

SQL> select a.user_id,
  2         a.offer_id,
  3         a.status,
  4         a.status_date,
  5         b.status next_status,
  6         b.status_date next_status_date
  7    from mytable a
  8         left join mytable b
  9            on     a.user_id = b.user_id
 10               and a.offer_id = b.offer_id
 11               and a.status_date < b.status_date
 12   where a.status = 'Active';

   USER_ID OFFER_ID   STATUS   STATUS_DATE     NEXT_STATUS     NEXT_STATUS_DATE
---------- ---------- -------- --------------- --------------- ----------------
         1 offer_1    Active   01/01/2021      Deactive        01/01/2022
         1 offer_2    Active   02/01/2022

SQL>