Solved

SQL query to determine consecutive usage

Posted on 2013-12-29
9
359 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
Comment Utility
Any one please help me with this issue. Thank you
0
 
LVL 31

Expert Comment

by:awking00
Comment Utility
>>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
Comment Utility
Please find the attached document for sample data and expected result are in  the last column
Sample.xlsx
0
 

Author Comment

by:Nagi2288
Comment Utility
Any one please help me with this issue. Thank you
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.

 
LVL 31

Expert Comment

by:awking00
Comment Utility
What is the datatype of MONTH_DW_ID?
0
 

Author Comment

by:Nagi2288
Comment Utility
MONTH_DW_ID data type number
0
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points
Comment Utility
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
Comment Utility
Thank you for the sql , i will test it and will post my comment on your solution.
0
 

Author Closing Comment

by:Nagi2288
Comment Utility
thank you
0

Featured Post

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Number Format 1 43
statspack purge automate 7 27
Oracle Syntax 8 37
sql query 9 18
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now