How to calculate no of cancel customers/total customers

Question

Good Morning,

I have the two tables customers (joined) and their orders (dateoforder- record perday)

CREATE TABLE Customers (
CID int NOT NULL PRIMARY KEY,
GroupName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CustomerStatus SMALLINT, ---1 Means Active, 0 Inactive
CustomerCancelDate Date,
DateofJoin DATE
);

INSERT INTO Customers Values(100, 'ABC123', 'John',30,1,NULL,'01/14/2022')
INSERT INTO Customers Values(101, 'ABC123', 'King',40,0,'02/19/2022','01/19/2022')
INSERT INTO Customers Values(102, 'XYZ456', 'SARA',55,1,'01/28/2022','01/21/2022')
INSERT INTO Customers Values(120, 'XYZ123', 'Jim',45,0,'02/28/2022','01/14/2022')
INSERT INTO Customers Values(121, 'XYZ123', 'Kim',21,1,NULL,'01/31/2022')

INSERT INTO Customers Values(103, 'ABC123', 'Diva',23,1,NULL,'02/04/2022')
INSERT INTO Customers Values(104, 'DEF123', 'Klip',19,1,NULL,'02/17/2022')
INSERT INTO Customers Values(105, 'ABC456', 'BobE',45,1,NULL,'02/27/2022')
INSERT INTO Customers Values(106, 'ABC123', 'MIla',20,1,NULL,'02/16/2022')
INSERT INTO Customers Values(107, 'DEF123', 'Yore',19,1,NULL,'02/19/2022')
INSERT INTO Customers Values(131, 'ABC123', 'Soni',23,1,NULL,'02/04/2022')
INSERT INTO Customers Values(132, 'ABC123', 'Mar',27,0,'02/16/2022','02/16/2022')
INSERT INTO Customers Values(133, 'ABC123', 'Pik',29,1,NULL,'02/11/2022')
INSERT INTO Customers Values(134, 'QQQ123', 'Ygi',33,1,NULL,'02/24/2022')
INSERT INTO Customers Values(135, 'ABCQQQ', 'Bip',45,0,'03/24/2022','02/14/2022')

INSERT INTO Customers Values(108, 'ABC456', 'Hams',21,1,NULL,'03/11/2022')
INSERT INTO Customers Values(109, 'ABC123', 'Zand',33,1,NULL,'03/04/2022')
INSERT INTO Customers Values(110, 'ABC123', 'Loda',44,0,'03/19/2022','03/07/2022')
INSERT INTO Customers Values(111, 'DEF456', 'Mopa',19,1,NULL,'03/24/2022')
INSERT INTO Customers Values(112, 'DEF456', 'Kodi',21,0,'03/31/2022','03/29/2022')
INSERT INTO Customers Values(136, 'DEF456', 'Rav',31,0,'04/31/2022','03/29/2022')

I am trying to see C H U R N rate per month in other words what is c h u r n value (calculated column) and c h u r n rate %

which is calculation formula is (number of canceled / total number of customers)

https://www.profitwell.com/customer-churn/calculate-churn-rate?hs_amp=true#Four-ways-to-calculate-churn
we are trying to get the value by first method "the simple way"

Please help with it.

Thank you in advance,
A.Sita

Answer 1

Hi @Jennis
Check this query:

;WITH CTE1 AS  
(  
 SELECT YEAR(DateofJoin) AS [YEAR],MONTH(DateofJoin) AS [MONTH],COUNT(DateofJoin) AS [New Customers]  
 FROM Customers  
 GROUP BY YEAR(DateofJoin),MONTH(DateofJoin)  
),CTE2 AS  
(  
 SELECT YEAR(CustomerCancelDate) AS [YEAR],MONTH(CustomerCancelDate) AS [MONTH],COUNT(CustomerCancelDate) AS [New Churn]  
 FROM Customers  
 WHERE CustomerCancelDate IS NOT NULL  
 GROUP BY YEAR(CustomerCancelDate),MONTH(CustomerCancelDate)  
),CTE3 AS  
(  
SELECT C1.YEAR,C1.MONTH,C1.[New Customers],C2.[New Churn]  
      ,SUM(C1.[New Customers]-C2.[New Churn])OVER(ORDER BY C1.[YEAR],C1.[MONTH]) AS [Total Customers]   
FROM CTE1 C1 JOIN CTE2 C2 ON C1.YEAR=C2.YEAR AND C1.MONTH=C2.MONTH  
)  
SELECT *,CAST([New Churn]*1.0/LAG([Total Customers])OVER(ORDER BY [YEAR],[MONTH])AS DECIMAL(10,2)) AS [ChurnRate]  
FROM CTE3  

Output:

Note: The Churn Rate of 2022 Jan is null, because there is no 2021 Dec datas.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you very much Lihong. I appreciate you help in it. is there any way can you help me in terms of sql can you please share you email id?

Thank you again.