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');

Select allOpen in new window