How to calculate no of cancel customers/total customers

21 Reputation points
2022-09-07T02:27:52.087+00:00

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"

A.Sita

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions

1. 22,541 Reputation points Microsoft Vendor
2022-09-07T09:22:23.993+00:00

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