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?
 
SharathConnect With a Mentor Data 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
0
 
Nagi2288Author Commented:
Any one please help me with this issue. Thank you
0
 
awking00Commented:
>>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?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Nagi2288Author Commented:
Please find the attached document for sample data and expected result are in  the last column
Sample.xlsx
0
 
Nagi2288Author Commented:
Any one please help me with this issue. Thank you
0
 
awking00Commented:
What is the datatype of MONTH_DW_ID?
0
 
Nagi2288Author Commented:
MONTH_DW_ID data type number
0
 
Nagi2288Author Commented:
Thank you for the sql , i will test it and will post my comment on your solution.
0
 
Nagi2288Author Commented:
thank you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.