How to calculate no of cancel customers/total customers

Jennis 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"

Please help with it.

Thank you in advance,
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
{count} votes

Accepted answer
  1. CosmogHong-MSFT 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:
    238544-image.png

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Jennis 21 Reputation points
    2022-09-09T03:16:22.61+00:00

    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.