How to Find Consecutive count of a column based on a their status

Question

I have a table containing various sites and billing information of those sites. Ex:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    6
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    7

What I need to find: I need to find consecutive count for each site based on their Reason_Type and billing_date. For example:If I receive any bill on 2015-10-06 whose Reason tyoe is RIGHT_BILL. So I need to find how many past consecutive cases were there for this scenario. For example If I take billing_date =2015-03-01 its showing me rank as 2 which is right but again If i go and check for 2015-04-03 its showing 2 which is wrong it should also show as one since the last month billing_date was having Zero_Bill.

Expected output:

 site_key |    billing_date     | Reason_type | rank
----------+---------------------+-------------+------
        3 | 2015-01-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-02-03 00:00:00 | Zero_Bill   |    1
        3 | 2015-03-01 00:00:00 | Zero_Bill   |    2
        3 | 2015-04-03 00:00:00 | RIGHT_BILL  |    1
        3 | 2015-05-03 00:00:00 | RIGHT_BILL  |    2
        3 | 2015-06-03 00:00:00 | RIGHT_BILL  |    3
        3 | 2015-07-03 00:00:00 | RIGHT_BILL  |    4
        3 | 2015-08-03 00:00:00 | RIGHT_BILL  |    5
        3 | 2015-10-06 00:00:00 | RIGHT_BILL  |    6

I tried:

Select site_key,
       billing_date,
       Reason_type, 
       Dense_rank() over (partition by site_key,billing_date,reason_type order by billing_date) as rank 
from indus_Dev_analytics.AVG_SCN  
where site_key=3 
order  by 1,2;

Answer 1

The problem in your query actually comes from the first row. It has the same Reason_type so it gives the extra ranking to the bottom records.

I think you can use CONDITIONAL_CHANGE_EVENT, maybe like:

select *,
    dense_rank() 
        over (partition by site,change_num,reason_type order by billing_date) as rank 
from (
   Select site,
     billing_date,
     Reason_type, 
     CONDITIONAL_CHANGE_EVENT(reason_type) 
         over (partition by site order by billing_date) as change_num
   from AVG_SCN  
   where site=3 
) foo 
order  by 1,2;