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 VARCH

Solution 1:

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_descPAID 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,
         casewhen prd_desc ='PAID'then greatest(0, -1+count(casewhen event_type_cd <>'exp'then1end) 
                      over (partitionby reg_id, pkg_des, prd_desc
                            orderby     event_date ascrowsbetween unbounded preceding and0 preceding)
                   )
              else0endas renewal_cnt,
         casewhenlag(prd_desc) over (partitionby reg_id, pkg_des
                                         orderby     event_date asc) ='PAID'and prd_desc ='PAID'and event_type_cd ='renewal'then1else0end is_ren, 
         casewhenlag(prd_desc) over (partitionby reg_id, pkg_des
                                         orderby     event_date asc) ='trail'and prd_desc ='PAID'then1else0end is_conv
from     temp
orderby 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  |   01  |  CC     |  trail   | 12-13-2012 |  exp          |       0     |     0  |   01  |  CC     |  PAID    | 12-14-2012 |  upsell       |       0     |     0  |   11  |  CC     |  PAID    | 12-15-2012 |  exp          |       0     |     0  |   0*
    1  |  CC     |  PAID    | 12-16-2012 |  renewal      |       1     |     1  |   01  |  CC     |  PAID    | 12-17-2012 |  renewal      |       2     |     1  |   01  |  aa     |  trail   | 12-12-2012 |  new sub      |       0     |     0  |   01  |  aa     |  trail   | 12-13-2012 |  exp          |       0     |     0  |   01  |  aa     |  PAID    | 12-14-2012 |  renewal      |       0     |     0  |   11  |  aa     |  PAID    | 12-15-2012 |  renewal      |       1     |     1  |   01  |  aa     |  PAID    | 12-16-2012 |  upsell       |       2     |     0* |   01  |  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.

Solution 2:

In Oracle 11g you could use recursive query like below:

with 
  t as (
      selectrow_number() over (partitionby pkg_des orderby event_date) rn, 
             reg_id, pkg_des, prd_desc, event_date, event_type_cd 
        from temp),
  th (rn, reg_id, pkg_des, prd_desc, event_date, event_type_cd, ren_cnt, is_ren, is_conv) 
    as (
      select rn, reg_id, pkg_des, prd_desc, event_date, event_type_cd, 0, 0, 0from t where rn =1unionallselect t.rn, t.reg_id, t.pkg_des, t.prd_desc, t.event_date, t.event_type_cd, 
             casewhen t.prd_desc ='PAID'and th.prd_desc ='PAID'and t.event_type_cd <>'exp'then th.ren_cnt +1else th.ren_cnt end,
             casewhen t.prd_desc ='PAID'and th.prd_desc ='PAID'and t.event_type_cd <>'exp'then1else0end,
             casewhen t.prd_desc ='PAID'and th.prd_desc ='trail'then1else0endfrom t join th on t.pkg_des = th.pkg_des and t.rn = th.rn +1 )
select pkg_des, prd_desc, event_date, event_type_cd, ren_cnt, is_ren, is_conv 
  from th orderby pkg_des desc, rn;

Output:

PKG_DESPRD_DESCEVENT_DATEEVENT_TYPE_CDREN_CNTIS_RENIS_CONV------------------------------------------------------------------------------------CCtrail2016-12-12  newsub000CCtrail2016-12-13  exp000CCPAID2016-12-14  upsell001CCPAID2016-12-15  exp000CCPAID2016-12-16  renewal110CCPAID2016-12-17  renewal210aatrail2016-12-12  newsub000aatrail2016-12-13  exp000aaPAID2016-12-14  renewal001aaPAID2016-12-15  renewal110aaPAID2016-12-16  upsell210aaPAID2016-12-17  renewal310

Subquery T only numbers rows in proper order. Main subquery TH is recursive. There is difference in row 4, in column is_conv, but I followed Your rules, so the conditions in case when may need little adjustments.

Explanations and examples of recursive CTE: 1, 2.