1

I know I have to use CONNECT BY PRIOR in this query, but I'm not sure how to implement it.

We have customers who purchase monthly subscriptions, and those get auto-renewed each month. We have a log table which can show what your current order ID is and what your previous order ID is. So, table records could look like this:

CUSTOMER ID: 1     ORDER ID: 123   PREV_ORDER_ID:       STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 456   PREV_ORDER_ID: 123   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 789   PREV_ORDER_ID: 456   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 888   PREV_ORDER_ID: 789   STATUS: Complete
CUSTOMER ID: 1     ORDER ID: 999   PREV_ORDER_ID: 888   STATUS: Active

I am looking to count how many customers have had at least 13 months of consecutive subscriptions, with no gaps with the most recent subscription will have an "Active" status. If there is a break in subscriptions, the PREV_ORDER_ID will be NULL.

I'm hoping to do this in a query, and not having to write an anonymous block for it.

Many thanks!

rgettman
  • 176,041
  • 30
  • 275
  • 357
Landon Statis
  • 683
  • 2
  • 10
  • 25

2 Answers2

2

You could do something like this (using your actual table and column names instead of the with clause from the query, which you should delete). Note that the hierarchical recursion starts from the end (from 'Active' status) and proceeds backwards; in my query I stop it at level 4 since I didn't feel like writing enough rows to get to level 13. Of course, you will have to replace 4 with 13 in the where clause.

with
  test_data (customer_id, order_id, prev_order_id, status) as (
    select 1, 123, null, 'Complete' from dual union all
    select 1, 456,  123, 'Complete' from dual union all
    select 1, 789,  456, 'Complete' from dual union all
    select 1, 888,  789, 'Complete' from dual union all
    select 1, 999,  888, 'Active'   from dual union all
    select 2, 100, null, 'Complete' from dual union all
    select 2, 200,  100, 'Active'   from dual union all
    select 5, 105, null, 'Complete' from dual union all
    select 5, 106,  105, 'Complete' from dual union all
    select 5, 205, null, 'Complete' from dual union all
    select 5, 206,  205, 'Active'   from dual
  )
select  customer_id
from    test_data
where   level = 4
start   with status = 'Active'
connect by customer_id = prior customer_id and order_id = prior prev_order_id
;

CUSTOMER_ID
-----------
          1
0

More data and tests are needed.

Maybe it will help you

CREATE TABLE CUSTOMER_LOG
(
CUSTOMER_ID number(5),
ORDER_ID number(5),
PREV_ORDER_ID number(5),
STATUS VARCHAR(50)
);

INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,123,NULL, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,456,123, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,789,456, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,888,789, 'Complete');
INSERT INTO CUSTOMER_LOG(CUSTOMER_ID,ORDER_ID,PREV_ORDER_ID,STATUS) values (1,999,888, 'Active');

Select
l.*,
(
select count(*)
from CUSTOMER_LOG s
where s.customer_id=1
start with s.ORDER_ID=l.ORDER_ID
connect by s.ORDER_ID= prior s.PREV_ORDER_ID   
) QTDE
from CUSTOMER_LOG l
where l.status='Active'
Samada
  • 56
  • 6