Solved

SQL query to determine consecutive usage

Posted on 2013-12-29
9
367 Views
Last Modified: 2014-01-07
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

0
Comment
Question by:Nagi2288
  • 6
  • 2
9 Comments
 

Author Comment

by:Nagi2288
ID: 39746054
Any one please help me with this issue. Thank you
0
 
LVL 32

Expert Comment

by:awking00
ID: 39746260
>>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
 

Author Comment

by:Nagi2288
ID: 39746493
Please find the attached document for sample data and expected result are in  the last column
Sample.xlsx
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:Nagi2288
ID: 39748420
Any one please help me with this issue. Thank you
0
 
LVL 32

Expert Comment

by:awking00
ID: 39748535
What is the datatype of MONTH_DW_ID?
0
 

Author Comment

by:Nagi2288
ID: 39748651
MONTH_DW_ID data type number
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
ID: 39752210
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
 

Author Comment

by:Nagi2288
ID: 39760263
Thank you for the sql , i will test it and will post my comment on your solution.
0
 

Author Closing Comment

by:Nagi2288
ID: 39762459
thank you
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
JDeveloper 12c for 32 bit 4 84
selective queries 7 27
Can anyone please tell me what does below Stored Procedure does? 4 29
SQL Developer 6 48
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question