beckham beckham - 3 months ago 9
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

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.

Comments