How to calculate Cumulated count of Id from two different temp tables using same source table-CodePudding

My Table is like:

ID	Type	month
100	activate	01-11-2020
100	Paid	01-03-2021
100	Paid	01-06-2021
101	activate	01-03-2021
102	activate	01-04-2021
102	paid	01-04-2021
103	activate	01-04-2021
103	paid	01-06-2021

Now I have two temp tables:

   tbl1 as (
Select *,case when row_number() over(partition by id order by month)=1 then 1 else null End as Cont1 from table where type='activate'),
    
tbl2 as (select *,,case when row_number() over(partition by id order by month)=1 then 1 else null End as Cont2 from table where type='paid')
   
 select tbl1.id ,
    tbl1.type,
    tbl1.month as activate_month,
    tbl2.month as paid_month,
   -- datediff('month',tb1.month,tbl2.month) month_diff,
    Sum(Cont1) over (partition by 1 order by tbl1.month rows unbounded preceding) as distA,
    Sum(Cont2) over (partition by 1 order by tbl2.month rows unbounded preceding) as distP
    from tbl1 ta
    left join tbl2 tp on ta.id=tp.id

Scenario:
for type='activate' entry of id will exist only once but for type='paid' there might be multiple entries, so I want to count this id in cumulative order in such a way that we can consider the first entry in a count with a month.

Result I want:

ID	Type	Amonth	Pmonth	countA	countp
100	activate	01-11-2020	01-03-2021	1	1
101	activate	01-03-2021	null	2	0
102	activate	01-04-2021	01-04-2021	3	2
103	activate	01-04-2021	01-06-2021	4	3

CodePudding user response：

You don't really need 2 CTE's for this.
An aggregation of a self-join will be sufficient.

You see, the window functions (f.e. row_number, dense_rank) are processed after aggregation. So you can use them also on a MIN.

For example:

select activate.id, activate.type
, min(activate.month) as activate_month
, min(paid.month) as activate_month
, row_number() over (order by min(activate.month) asc) as rn_activate
, case when min(paid.month) is not null
  then row_number() over (order by min(paid.month) asc)
  else 0 
  end as rn_paid
from yourtable as activate
left join yourtable as paid
  on paid.id = activate.id
 and paid.type = 'paid'
where activate.type = 'activate'
group by activate.id, activate.type
order by min(activate.month) asc;

id	type	activate_month	activate_month	rn_activate	rn_paid
100	activate	2020-11-01	2021-03-01	1	1
101	activate	2021-03-01	null	2	0
102	activate	2021-04-01	2021-04-01	3	2
103	activate	2021-04-01	2021-06-01	4	3

Demo on db<>fiddle here

CodePudding user response：

If I understood correctly, this can be done using analytic functions with outer apply.

Select Tbl.ID, Tbl.Type, Min(Tbl.month) As Amonth, Min(T.Pmonth) As Pmonth, 
       Count(Tbl.ID) Over (Order by Tbl.ID Rows Unbounded Preceding) As countA, 
       Case When T.ID Is Not Null 
            Then Count(T.ID) Over (Order by T.ID Rows Unbounded Preceding) 
            Else 0 
       End As countp
From Tbl Outer Apply (Select ID, Min(month) As Pmonth 
                      From Tbl As T 
                      Where ID=Tbl.ID 
                            And Type='Paid' 
                      Group by ID) As T
Where Tbl.Type='activate'
Group by Tbl.ID, T.ID, Tbl.Type
Order by Tbl.ID

Data output:

ID          Type       Amonth     Pmonth     countA      countp
----------- ---------- ---------- ---------- ----------- -----------
100         activate   2020-11-01 2021-03-01 1           1
101         activate   2021-03-01 NULL       2           0
102         activate   2021-04-01 2021-04-01 3           2
103         activate   2021-04-01 2021-06-01 4           3