Month on month retention in a year in SQL-CodePudding

How can we calculate the month on month cumulative retention rate in SQL

Bill_Date Customer_id
2021-01-01     1  
2021-01-23     2
2021-01-29     3
2021-02-17     1
2021-02-19     2
2021-03-01     3

Retention Rate= (Total Number of unique Customers in present Month)/(Total Number of Customers in previous Months)

Expected Output

January : 100% 
February : 66.7%
March : 25%

February =(Unique customers in feb)/((Unique customers in jan)
March=(Unique customers in march)/((Unique customers in jan) (Unique
 customers in feb)

CodePudding user response：

Consider a window function for cumulative sum of unique customer per year/month:

WITH sub AS (
  SELECT YEAR(c.Bill_Date) AS bill_year, 
         MONTH(c.Bill_Date) AS bill_month,
         COUNT(DISTINCT c.customer_id) AS unq_customers
  FROM customer_bills c
  GROUP BY YEAR(c.Bill_Date), 
           MONTH(c.Bill_Date)
)

SELECT bill_year,
       bill_month,
       unq_customers,
       IFNULL(
         unq_customers /
         (SUM(unq_customers) OVER(ORDER BY bill_month) -
          unq_customers),
         1
       ) * 100 AS retention_rate
FROM sub

Online Demo