How to calculate churn Rate (Retention) in db2?

Question

Here are info I have: Group Name /YYYYMM / Contracts. Now I need the last 12 months churn rate and SQL runs efficiently.

I wrote this , but not working properly:

SELECT GROUP_NAME,ID, YRMO,SUM(FLOAT(CTC)) AS CTC_MOS,
MAX(CASE WHEN YRMO = VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') THEN VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') END)  AS YRMO_PRIOR,
SUM(CASE WHEN YRMO = VARCHAR_FORMAT(DATE(TO_DATE(@ENR_END_INC,'YYYYMM'))- 12 MONTHS, 'YYYYMM') THEN CTC END)  AS CTC_PRIOR

FROM TABLE1

below is sample data if needed:

GP  YRMO    CTC
B1  202011  8
B1  202012  10
B1  202101  11
B1  202102  11
B1  202103  11
B1  202104  11
B1  202105  12
B1  202106  12
B1  202107  12
B1  202108  12
B1  202109  12
B1  202110  12
B1  202111  12
B1  202112  11
B1  202201  11
B1  202202  11
B1  202203  11
B1  202204  13
B1  202205  14
B1  202206  15
B1  202207  22
B1  202208  23
B1  202209  23
B1  202210  23
B1  202211  24
B1  202311  27

How can I produce something like below:

Answer 1

The solution without JOIN.

WITH T (GP, YRMO, CTC) AS
(
  VALUES
  ('B1', 202011, 8)
, ('B1', 202012, 10)
, ('B1', 202111, 12)
, ('B1', 202112, 11)
, ('B1', 202211, 24)
, ('B1', 202212, 0)
)
SELECT 
  T.*
, COALESCE (100 * (CTC_C - CTC_P) / NULLIF (CTC_P, 0), 100)
  AS RATE 
FROM
(
SELECT 
  GP, YRMO
, CTC AS CTC_C
, YRMO - 100 AS YRMO_P
, COALESCE (LAG (CTC) OVER (PARTITION BY GP, MOD (YRMO, 100) ORDER BY YRMO), 0)
  AS CTC_P
FROM T
) T
ORDER BY GP, YRMO

GP	YRMO	CTC_C	YRMO_P	CTC_P	RATE
B1	202011	8	201911	0	100
B1	202012	10	201912	0	100
B1	202111	12	202011	8	50
B1	202112	11	202012	10	10
B1	202211	24	202111	12	100
B1	202212	0	202112	11	-100

fiddle

Update:

For 6-month you may use the following expressions:

, INT (TO_CHAR (TO_DATE (VARCHAR (YRMO) || '01', 'YYYYMMDD') - 6 MONTH, 'YYYYMM'))
  AS YRMO_P
, COALESCE
  (
    MAX (CTC) OVER 
    (
      PARTITION BY GP
      ORDER BY MOD (YRMO, 100) + (YRMO / 100) * 12
      RANGE BETWEEN 6 PRECEDING AND 6 PRECEDING 
    )
  , 0
  ) AS CTC_P

The idea is to enumerate months sequentially without gaps with the ORDER BY expression to use RANGE inside MAX () OVER ().

Answer 2

You can do:

select
  coalesce(c.gp, p.gp) as gp,
  coalesce(c.yrmo, p.yrmo + 100) as yrmo_current,
  coalesce(c.sum_of_ctc, 0) as ctc_current,
  coalesce(p.yrmo, c.yrmo - 100) as yrmo_prior,
  coalesce(p.sum_of_ctc, 0) as ctc_prior,
  case when p.sum_of_ctc = 0 or p.sum_of_ctc is null then 1.0
       when c.sum_of_ctc = 0 or c.sum_of_ctc is null then -1.0
       else (1.0 * c.sum_of_ctc - p.sum_of_ctc) / p.sum_of_ctc
  end * 100 as churn_rate
from t c
full join t p on p.gp = c.gp and p.yrmo = c.yrmo - 100
order by gp, yrmo_current

Result:

GP  YRMO_CURRENT  CTC_CURRENT  YRMO_PRIOR  CTC_PRIOR  CHURN_RATE              
 --- ------------- ------------ ----------- ---------- ----------------------- 
 B1  201911        6            201811      0          100.000000000000000000  
 B1  201912        8            201812      0          100.000000000000000000  
 B1  202001        8            201901      0          100.000000000000000000  
 B1  202002        8            201902      0          100.000000000000000000  
 B1  202003        8            201903      0          100.000000000000000000  
 B1  202004        8            201904      0          100.000000000000000000  
 B1  202005        9            201905      0          100.000000000000000000  
 B1  202006        9            201906      0          100.000000000000000000  
 B1  202007        9            201907      0          100.000000000000000000  
 B1  202008        9            201908      0          100.000000000000000000  
 B1  202009        8            201909      0          100.000000000000000000  
 B1  202010        8            201910      0          100.000000000000000000  
 B1  202011        8            201911      6          33.333333333333333300   
 B1  202012        10           201912      8          25.000000000000000000   
 B1  202101        11           202001      8          37.500000000000000000   
 B1  202102        11           202002      8          37.500000000000000000   
 B1  202103        11           202003      8          37.500000000000000000   
 B1  202104        11           202004      8          37.500000000000000000   
 B1  202105        12           202005      9          33.333333333333333300   
 B1  202106        12           202006      9          33.333333333333333300   
 B1  202107        12           202007      9          33.333333333333333300   
 B1  202108        12           202008      9          33.333333333333333300   
 B1  202109        12           202009      8          50.000000000000000000   
 B1  202110        12           202010      8          50.000000000000000000   
 B1  202111        12           202011      8          50.000000000000000000   
 B1  202112        11           202012      10         10.000000000000000000   
 B1  202201        11           202101      11         0.000000000000000000    
 B1  202202        11           202102      11         0.000000000000000000    
 B1  202203        11           202103      11         0.000000000000000000    
 B1  202204        13           202104      11         18.181818181818181800   
 B1  202205        14           202105      12         16.666666666666666600   
 B1  202206        15           202106      12         25.000000000000000000   
 B1  202207        22           202107      12         83.333333333333333300   
 B1  202208        23           202108      12         91.666666666666666600   
 B1  202209        23           202109      12         91.666666666666666600   
 B1  202210        23           202110      12         91.666666666666666600   
 B1  202211        24           202111      12         100.000000000000000000  
 B1  202212        0            202112      11         -100.000000000000000000 
 B1  202301        0            202201      11         -100.000000000000000000 
 B1  202302        0            202202      11         -100.000000000000000000 
 B1  202303        0            202203      11         -100.000000000000000000 
 B1  202304        0            202204      13         -100.000000000000000000 
 B1  202305        0            202205      14         -100.000000000000000000 
 B1  202306        0            202206      15         -100.000000000000000000 
 B1  202307        0            202207      22         -100.000000000000000000 
 B1  202308        0            202208      23         -100.000000000000000000 
 B1  202309        0            202209      23         -100.000000000000000000 
 B1  202310        0            202210      23         -100.000000000000000000 
 B1  202311        27           202211      24         12.500000000000000000   
 B1  202411        0            202311      27         -100.000000000000000000 

See running example at db<>fiddle.