Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL query to determine consecutive usage

Posted on 2013-12-29
9
Medium Priority
?
374 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 41

Accepted Solution

by:
Sharath earned 2000 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

916 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