CODEWITHSUNDEEP
sqloracle-databaseanalytic-functions
user2018454
user2018454

Reputation: 25

Next action using analytic functions

For the need of a cohort analysis, I'm trying to get the next action of each customer (unsubscription, upgrade, downgrade...)

I have a monhtly snapshot with the following data : 

customer | month      | last_action   | last_action_date
1          01-01-2012   subscription    01-01-2012
1          02-01-2012   subscription    01-01-2012
1          03-01-2012   subscription    01-01-2012
1          04-01-2012   downgrade       04-01-2012
1          05-01-2012   downgrade       04-01-2012
1          06-01-2012   downgrade       04-01-2012
1          07-01-2012   unsubscription  07-01-2012

As you can see, the action is known only at the month it is done, at the month 01-01-2012 we don't know yet the customer downgraded in 04-01-2012 so we can't analyse his usage behavior relative to his downgrade month. Same thing for unsubscription.

The needed dataset is the following :

customer | month      | downgrade_date   | unsubscription_date
1          01-01-2012   04-01-2012         07-01-2012
1          02-01-2012   04-01-2012         07-01-2012
1          03-01-2012   04-01-2012         07-01-2012
1          04-01-2012   12-31-9999         07-01-2012
1          05-01-2012   12-31-9999         07-01-2012
1          06-01-2012   12-31-9999         07-01-2012
1          07-01-2012   12-31-9999         07-01-2012

I could have easily the unsubscription date with the last_value analytic function but didn't find a way to get the downgrade date.

Here is my SQL Query :

SELECT month_id, 
       customer_id,
       CASE 
         WHEN LAST_VALUE(last_action) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) = 'unsubscription' THEN LAST_VALUE(last_action_date) OVER (PARTITION BY customer_id ORDER BY month_id RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)    
         ELSE TO_DATE('99991231', 'yyyymmdd')
       END unsubscription_date
FROM my_table
;

Any way to get the "next" action date like in the "downgrade_date".

I'm using Oracle.

Upvotes: 2

Views: 115

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

In oracle 11, you can do this with lead() and the ignore nulls option:

select customer, MONTH,
       lead(case when last_action = 'downgrade' then last_action_date end ignore nulls) over
                 (partition by customer order by month desc) as downgrade_date,
       lead(case when last_action = 'unsubscription' then last_action_date end ignore nulls) over
                 (partition by customer order by month desc) as downgrade_date,
from my_table t

If you don't have ignore nulls, you can do something similar with min():

select customer, MONTH,
       min(case when last_action = 'downgrade' then last_action_date end) over
                (partition by customer order by month range between current and unbounded following
                ) as downgrade_date,
       min(case when last_action = 'unsubscription' then last_action_date end) over
                (partition by customer order by month range between current and unbounded following
                ) as unsubscription_date
from my_table t

Upvotes: 1

Related Questions