beckham beckham - 1 year ago 116
SQL Question

How to assign the counter based on a condition

Source Table DDL :-

CREATE TABLE temp (
REG_ID NUMBER(5) ,
Pkg_DES VARCHAR2(15),
PRD_DESC VARCHAR2(15)
EVENT_DATE DATE,
event_type_cd VARCHAR2(15)
)


I have insert statements too below -

insert into temp (REG_ID, Pkg_DES, PRD_DESC, EVENT_DATE, event_type_cd)
select 1 , 'CC' , 'trail' , to_date('12/12/2016', 'mm/dd/yyyy') , 'new sub' from dual union all
select 1 , 'CC' , 'trail' ,to_date('12/13/2016', 'mm/dd/yyyy') , 'exp' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/14/2016', 'mm/dd/yyyy') , 'upsell' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/15/2016', 'mm/dd/yyyy'), 'exp' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/16/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'CC' , 'PAID' , to_date('12/17/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'trail' , to_date('12/12/2016', 'mm/dd/yyyy') , 'new sub' from dual union all
select 1 , 'aa' , 'trail' , to_date('12/13/2016', 'mm/dd/yyyy') , 'exp' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/14/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/15/2016', 'mm/dd/yyyy') , 'renewal' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/16/2016', 'mm/dd/yyyy') , 'upsell' from dual union all
select 1 , 'aa' , 'PAID' , to_date('12/17/2016', 'mm/dd/yyyy') , 'renewal' from dual;


I have the requirement like this below

REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd
-------+---------+----------+------------+--------------
1 | CC | trail | 12-12-2012 | new sub
1 | CC | trail | 12-13-2012 | exp
1 | CC | PAID | 12-14-2012 | upsell
1 | CC | PAID | 12-15-2012 | exp
1 | CC | PAID | 12-16-2012 | renewal
1 | CC | PAID | 12-17-2012 | renewal
1 | aa | trail | 12-12-2012 | new sub
1 | aa | trail | 12-13-2012 | exp
1 | aa | PAID | 12-14-2012 | renewal
1 | aa | PAID | 12-15-2012 | renewal
1 | aa | PAID | 12-16-2012 | upsell
1 | aa | PAID | 12-17-2012 | renewal


The output I need is like below:

REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd | renewal_cnt | is_ren | is_conv
-------+---------+----------+------------+---------------+-------------+--------+--------
1 | CC | trail | 12-12-2012 | new sub | 0 | 0 | 0
1 | CC | trail | 12-13-2012 | exp | 0 | 0 | 0
1 | CC | PAID | 12-14-2012 | upsell | 0 | 0 | 1
1 | CC | PAID | 12-15-2012 | exp | 0 | 0 | 1
1 | CC | PAID | 12-16-2012 | renewal | 1 | 1 | 0
1 | CC | PAID | 12-17-2012 | renewal | 2 | 1 | 0
1 | aa | trail | 12-12-2012 | new sub | 0 | 0 | 0
1 | aa | trail | 12-13-2012 | exp | 0 | 0 | 0
1 | aa | PAID | 12-14-2012 | renewal | 0 | 0 | 1
1 | aa | PAID | 12-15-2012 | renewal | 1 | 1 | 0
1 | aa | PAID | 12-16-2012 | upsell | 2 | 1 | 0
1 | aa | PAID | 12-17-2012 | renewal | 3 | 1 | 0



  • Conversion Logic :- If a PRD_DESC is changed from Trail to Paid for the 1st time, then it is called CONVERSION product

  • Renewal Logic :- If a Paid Product is renewed from Paid to Paid, then it is called RENEWAL product

  • renewal_cnt should start only at the PAID to PAID product. If the PAID to PAID is having event_type_cd as exp then the counter shouldn't increment. If the trail to paid is having event_type_cd as renewal then also it should be zero only. the event's are ordered by event_date

  • is_conv should be set to 1 for the 1st Trail to Paid product.

  • is ren should be set to 1 for the paid to paid product.



Does someone have the idea how to achieve this?

Answer Source

The solution of @PonderStibbons is fine, but as I had made one myself, not based on recursion, I post it as well. Note that there will be differences for other data sets. Notably, this query assumes that within a given range of same pkg_des records, the group of records with prd_desc PAID are not interrupted by non-PAID values. This is not an assumption made in the recursive solution, which could be an important factor to discard my solution:

select   reg_id, pkg_des, prd_desc, event_date, event_type_cd,
         case when prd_desc = 'PAID'
              then -1+count(case when event_type_cd <> 'exp' then 1 end) 
                      over (partition by reg_id, pkg_des, prd_desc
                            order by     event_date asc
                            rows between unbounded preceding and 0 preceding)
              else 0
         end as renewal_cnt,
         case when   lag(prd_desc) over (partition by reg_id, pkg_des
                                         order by     event_date asc) = 'PAID' 
                 and prd_desc = 'PAID'
                 and event_type_cd = 'renewal'
              then 1 
              else 0 
         end is_ren, 
         case when   lag(prd_desc) over (partition by reg_id, pkg_des
                                         order by     event_date asc) = 'trail'
                 and prd_desc = 'PAID'
              then 1
              else 0
         end is_conv
from     temp
order by reg_id asc,
         pkg_des desc,
         event_date asc;

Output is the same for the given sample data:

REG_ID | Pkg_DES | PRD_DESC | EVENT_DATE | event_type_cd | renewal_cnt | is_ren | is_conv
-------+---------+----------+------------+---------------+-------------+--------+--------   
    1  |  CC     |  trail   | 12-12-2012 |  new sub      |       0     |     0  |   0
    1  |  CC     |  trail   | 12-13-2012 |  exp          |       0     |     0  |   0
    1  |  CC     |  PAID    | 12-14-2012 |  upsell       |       0     |     0  |   1
    1  |  CC     |  PAID    | 12-15-2012 |  exp          |       0     |     0  |   0*
    1  |  CC     |  PAID    | 12-16-2012 |  renewal      |       1     |     1  |   0
    1  |  CC     |  PAID    | 12-17-2012 |  renewal      |       2     |     1  |   0
    1  |  aa     |  trail   | 12-12-2012 |  new sub      |       0     |     0  |   0 
    1  |  aa     |  trail   | 12-13-2012 |  exp          |       0     |     0  |   0
    1  |  aa     |  PAID    | 12-14-2012 |  renewal      |       0     |     0  |   1
    1  |  aa     |  PAID    | 12-15-2012 |  renewal      |       1     |     1  |   0
    1  |  aa     |  PAID    | 12-16-2012 |  upsell       |       2     |     0* |   0 
    1  |  aa     |  PAID    | 12-17-2012 |  renewal      |       3     |     1  |   0

I added an asterisk where the output is different from what you listed in your question, but the above is the output when the rules are followed to the letter.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download