How to find consecutive count last 3 months count and last 6 months counts

Column names: Site_key. Billing_date ,Reason_type Things to Find: 1. Consecutive count for last 5 months: 2. Last 3 month Count 3. Last 6 month Count I have to find Consecutive count for last 5 months for a site_key based on the condition Reason_TYpe. There are 4 distinct Reason_type: Zero_bill Same_Units Units_Deviate Average_Bill\ I need to find for a particular site key say for ex: 3, billing_date is say 20-11-2015, and reason type ='Zero_Bill' 1. How many consecutive previous scenarios were there were site had Zero_Bill (excluding current month) 2. How many such Scenarios in last 3 months (including current months) 3. How many such Scenarios in last 6 months (including current months) Sample data: site_key | billing_date | Reason_type ----------+---------------------+--------------------- 3 | 2015-01-03 00:00:00 | AVERAGE_BILL 3 | 2015-02-03 00:00:00 | ZERO_Bill 3 | 2015-03-01 00:00:00 | ZERO_Bill 3 | 2015-04-03 00:00:00 | AVERAGE_BILL 3 | 2015-05-03 00:00:00 | AVERAGE_BILL 3 | 2015-06-03 00:00:00 | ZERO_BILL 3 | 2015-07-03 00:00:00 | ZERO_BILL 3 | 2015-08-03 00:00:00 | AVERAGE_BILL 3 | 2015-10-06 00:00:00 | ZERO_BILL Expected Output: site_key | billing_date | Reason_type |LAST_Consecutive| Last_3_Months | Last_6_months ----------+---------------------+----------------------- +----------------+---------------+-------------------- 3 | 2015-01-03 00:00:00 | AVERAGE_BILL |0 |1 |1 3 | 2015-02-03 00:00:00 | ZERO_Bill |0 |1 |1 3 | 2015-03-01 00:00:00 | ZERO_Bill |1 |2 |2 3 | 2015-04-03 00:00:00 | AVERAGE_BILL |0 |2 |2 3 | 2015-05-03 00:00:00 | AVERAGE_BILL |1 |2 |2 3 | 2015-06-03 00:00:00 | ZERO_BILL |0 |2 |3 3 | 2015-07-03 00:00:00 | ZERO_BILL |1 |2 |4 3 | 2015-08-03 00:00:00 | AVERAGE_BILL |0 |1 |3 3 | 2015-10-06 00:00:00 | ZERO_BILL |0 |3 |3 What I tried: For Consecutive count: Select site_key, billing_date, Reason_type, rank() over (partition by site_key,billing_date,reason_type order by billing_date) as consecutive_count from schema.AVG_SCN where site_key=3 order by 1,2; For Last_3_Months and Last_6_Months: Select site_key,Billing_Date,Case when cnt>3 then 3 else cnt end as Count_OF_3, Case when cnt>6 then 6 else cnt end as Count_OF_6 from ( Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Zero_Bill' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from Schema.AVG_SCN where reason_type='Average_Bill' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Units_Deviate' group by billing_date,site_key,Reason_TYPE union all Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Same_Units' group by billing_date,site_key,Reason_TYPE) as a;