How to calculate canceled accounts / total accounts

Question

Post reply

How to calculate canceled accounts / total accounts

asita

SSCarpal Tunnel

Points: 4144

September 7, 2022 at 2:31 am

#4084702

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,

A.Sita

This topic was modified 2 years, 2 months ago by Steve Jones - SSC Editor. Reason: format

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 1

what have you tested for this homework assignment??

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Good Morning,

I started here but still need to go if any techniques that would be great

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

Thanks you

This reply was modified 2 years, 2 months ago by Steve Jones - SSC Editor. Reason: code format

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 3

in this example, I've oriented everything towards its end of month date.

When working with date/datetime, stay in that data type as long as possible because of validation and appropriate builtin functions.

Declare @StartDate date = '2020-01-01'
;with cteDates as 
( --Declare @StartDate date = '2020-01-01' 
Select eomonth(dateadd(mm, n,  @StartDate)) MonthEnd
 from master.dbo.fn_DBA_Tally2(0,240) T
 Inner join ( Select min(EOMONTH(DateofJoin)) as MinMonth, max(EOMONTH(DateofJoin)) as MaxMonth
from #Customers
) C
on eomonth(dateadd(mm, n,  @StartDate)) between C.MinMonth and C.MaxMonth
 )
,cteStartdateMonth as (
Select *
, EOMONTH(CustomerCancelDate) CustomerCancelDatemm
, EOMONTH(DateofJoin) DateofJoinmm 
 from #Customers 
 )
, cteCanceledCustomersMonth as
( Select M.MonthEnd, count(*) as nChurnCustomers, avg(datediff(MM,eomonth(SdM.DateofJoin),eomonth(SdM.CustomerCancelDate))) AvgMonthsClient
from cteDates M
inner join cteStartdateMonth SdM
on SdM.DateofJoinmm <= M.MonthEnd
and eomonth(SdM.CustomerCancelDatemm) = M.MonthEnd 
group by M.MonthEnd
)
, cteActiveCustomersMonth as
( Select M.MonthEnd, count(*) as nCustomers, avg(datediff(MM,eomonth(SDM.DateofJoinmm),eomonth(M.MonthEnd))) AvgMonthsClient
from cteDates M
inner join cteStartdateMonth SdM
on SdM.DateofJoinmm <= M.MonthEnd
and ( SdM.CustomerCancelDatemm is null
OR SdM.CustomerCancelDatemm >= M.MonthEnd  )
group by M.MonthEnd
)

ps: I forgot to mention user defined table valued function "master.dbo.fn_DBA_Tally2" is an implementation of Jeff Modens "tally table" to provide a series of numbers

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

asita SSCarpal Tunnel Points: 4144 More actions · Answer 4

Thank you much Johan, I will follow your guidelines. Thank you

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 5

Johan Bijnens wrote:

ps: I forgot to mention user defined table valued function "master.dbo.fn_DBA_Tally2" is an implementation of Jeff Modens "tally table" to provide a series of numbers

If it's substantially different than most, I'd love to see "fn_DBA_Tally2", Johan. Heh... I can learn stuff, too! 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 6

The biggest optimization I've done is to have its name meet our naming conventions ∇

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 7

Thanks, Johan.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)