SQL query to determine consecutive usage

I have a requirement to find out number of months a customer has done 10 or more searches in a row.  If there is a gap between months then the counter should reset.

Attached is some sample data and the code that i have written .

CURR_MONTH_OW_SEARCH_CNT is a field which shows the number of searches that a particular customer ( ACCT_GRP_DW_ID ) per brand (BRAND_DW_ID) per  month (MONTH_DW_ID).

When ever there is a gap between the months (201301 201303) the counter should restart the count based on CURR_MONTH_OW_SEARCH_CNT.

Please review my code , i have used last_value function but missing something because i am not able to get the desired results.

Oracle 11g database
SELECT
  MONTH_DW_ID,
  ACCT_GRP_DW_ID,
  BRAND_DW_ID,
  CURR_MONTH_OW_SEARCH_CNT,
  PREV_MONTH_OW_SEARCH_CNT,
  USAGE_CNT,
  TOTAL_USAGE,
  CONSECUITIVE_USAGE
FROM
  (
    SELECT
      MONTH_DW_ID,
      ACCT_GRP_DW_ID,
      BRAND_DW_ID,
      CURR_MONTH_OW_SEARCH_CNT,
      PREV_MONTH_OW_SEARCH_CNT,
      USAGE_CNT,
      TOTAL_USAGE ,
      TOTAL_USAGE - NVL(Last_Value (
        CASE
          WHEN rn                     <> 1
          AND PREV_MONTH_OW_SEARCH_CNT = 0
          THEN TOTAL_USAGE
        END IGNORE NULLS) OVER (PARTITION BY ACCT_GRP_DW_ID, BRAND_DW_ID
      ORDER BY month_dw_id),0) AS CONSECUITIVE_USAGE
    FROM
      (
        SELECT
          MONTH_DW_ID,
          ACCT_GRP_DW_ID,
          BRAND_DW_ID,
          CURR_MONTH_OW_SEARCH_CNT,
          PREV_MONTH_OW_SEARCH_CNT,
          rn,
          --     CASE WHEN rn <> 1 AND PREV_MONTH_OW_SEARCH_CNT =0  THEN 0 ELSE
          -- usage_cnt END AS
          usage_cnt,
          SUM(
            CASE
              WHEN CURR_MONTH_OW_SEARCH_CNT>=10
              THEN 1
              ELSE 0
            END ) OVER ( PARTITION BY ACCT_GRP_DW_ID,BRAND_DW_ID ORDER BY
          month_dw_id) TOTAL_USAGE
        FROM
          (
            SELECT
              ACCT_GRP_DW_ID,
              BRAND_DW_ID,
              MONTH_DW_ID,
              CURR_MONTH_OW_SEARCH_CNT,
              row_number () OVER (PARTITION BY ACCT_GRP_DW_ID ORDER BY
              MONTH_DW_ID) AS rn,
              CASE
                WHEN months_between(to_date(TO_CHAR(month_dw_id
                  || '01'),'yyyy-mm-dd') ,to_date (TO_CHAR( NVL(LAG(month_dw_id
                  ) OVER (PARTITION BY ACCT_GRP_DW_ID, BRAND_DW_ID ORDER BY
                  month_dw_id),month_dw_id)
                  || '01'),'yyyy-mm-dd')) = 1
                THEN NVL(LAG(CURR_MONTH_OW_SEARCH_CNT ) OVER (PARTITION BY
                  ACCT_GRP_DW_ID, BRAND_DW_ID ORDER BY MONTH_DW_ID),0)
                ELSE 0
              END AS PREV_MONTH_OW_SEARCH_CNT,
              CASE
                WHEN CURR_MONTH_OW_SEARCH_CNT >= 10
                THEN 1
                ELSE 0
              END AS usage_cnt,
              0   AS TOTAL_USAGE,
              0   AS CONSECUITIVE_USAGE
            FROM
              (
                SELECT DISTINCT
                  SF.ACCT_GRP_DW_ID,
                  SF.BRAND_DW_ID,
                  SF.MONTH_DW_ID,
                  SUM(CURR_MONTH_OW_SEARCH_CNT) over ( partition BY
                  SF.ACCT_GRP_DW_ID, SF.BRAND_DW_ID, SF.MONTH_DW_ID ORDER BY
                  month_dw_id ) AS CURR_MONTH_OW_SEARCH_CNT,
                  0             AS PREV_MONTH_OW_SEARCH_CNT,
                  0             AS usage_cnt,
                  0             AS TOTAL_USAGE,
                  0             AS CONSECUITIVE_USAGE
                FROM
                  edw.OW_REPEAT_USAGE_USER_FACT SF
                WHERE
                  sf.month_dw_id BETWEEN 201301 AND 201312
              )
          )
      )
  )
WHERE
  ACCT_GRP_DW_ID IN ('9598910','9596918');

Open in new window

Nagi2288Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nagi2288Author Commented:
Any one please help me with this issue. Thank you
awking00Information Technology SpecialistCommented:
>>Attached is some sample data and the code that i have written . <<
I don't see the sample data. Could you provide it along with your expected results from that data?
Nagi2288Author Commented:
Please find the attached document for sample data and expected result are in  the last column
Sample.xlsx
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Nagi2288Author Commented:
Any one please help me with this issue. Thank you
awking00Information Technology SpecialistCommented:
What is the datatype of MONTH_DW_ID?
Nagi2288Author Commented:
MONTH_DW_ID data type number
SharathData EngineerCommented:
try this query.
WITH CTE 
     AS (SELECT t1.*, 
                ROW_NUMBER() 
                  OVER ( 
                    PARTITION BY t1.ACCT_GRP_DW_ID 
                    ORDER BY t1.MONTH_DW_ID) rn 
           FROM test t1 
          WHERE CURR_MONTH_OW_SEARCH_CNT >= 10), 
     CTE1 
     AS (SELECT t1.*, 
                ROW_NUMBER() 
                  OVER ( 
                    PARTITION BY t1.ACCT_GRP_DW_ID, t1.MONTH_DW_ID-t1.rn 
                    ORDER BY t1.MONTH_DW_ID) rn1 
           FROM CTE t1) 
SELECT * 
  FROM (SELECT MONTH_DW_ID,ACCT_GRP_DW_ID,BRAND_DW_ID,PREV_MONTH_OW_SEARCH_CNT,USAGE_CNT,CURR_MONTH_OW_SEARCH_CNT,rn1 Expected_Result 
          FROM CTE1 
        UNION ALL 
        SELECT MONTH_DW_ID,ACCT_GRP_DW_ID,BRAND_DW_ID,PREV_MONTH_OW_SEARCH_CNT,USAGE_CNT,CURR_MONTH_OW_SEARCH_CNT,0 
          FROM test 
         WHERE CURR_MONTH_OW_SEARCH_CNT < 10) 
 ORDER BY ACCT_GRP_DW_ID, 
          MONTH_DW_ID 

Open in new window

http://sqlfiddle.com/#!4/b0911/14

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Nagi2288Author Commented:
Thank you for the sql , i will test it and will post my comment on your solution.
Nagi2288Author Commented:
thank you
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.