# [SOLVED] Month on month retention in a year in SQL

## Issue

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)
``````

## Solution

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