Number of times customer changed subscription

Hi,

My thinking is that you want a self-join from the first row to the next row (and so on) so that you can see what the change is.

Given that there is no specific linking between rows, I'd look at a CTE that ranks the rows for each customer over date.

Then the join is
from cte_name as a -- row
left outer join cte_name as b -- next row
    on a.CustomerID = b.CustomerID
    and a.Rank + 1 = b.rank

HTH
  David

One way to look into the issue:-

Identify the count of Online as well as Paper types for each customer within each year. ( Assuming that type will be changed during consecutive entries ).

This can be identified by using something like the following.

Select	 CustomerID,  YEAR(SubscriptionChangeDate) , 
		 count ( Case when SubscriptionType = 'Online' then 1 else null end  ) as Online, 
		 count ( Case when SubscriptionType = 'Paper' then 1 else null end  ) as Paper
FROM CustomerTab
group by CustomerID , YEAR(SubscriptionChangeDate) , SubscriptionType 

Select allOpen in new window

Second part of the issue is, reduce the count by one for the first type of transaction, if it is a carry over from previous year. That should not be difficult. If you need support on that please post table creation script and sample data insert script.


OR
if you are using SQL 2012, OVER() clause with LEAD and LAG would be make the solution easier.

Thanks for the responses.

Below is the table script and insert data sql statements.
Can you please show me how to do the second part?

CREATE TABLE dbo.Customer 
(CustomerId				INT			NOT NULL,
 SubscriptionChangeDate DATETIME	NOT NULL,
 SubscriptionType		VARCHAR(20) NOT NULL
); 
 
 
INSERT INTO dbo.Customer 
VALUES
(1, '01-JAN-2012','Online'),
(1, '02-MAR-2012','Paper'),
(1, '10-Nov-2012','Online'),

(1, '04-Mar-2013','Paper'),
(1, '07-Aug-2013','Online'),

(2, '01-JAN-2014','Online'),

(3, '01-JUN-2013','Paper'),
(3, '01-SEP-2013','Online'),
(3, '05-NOv-2013','Paper'),
(3, '28-Dec-2013','Online');

Select allOpen in new window

The logic that you have shown seems to count the number of records with SubscriptionType = 'Online' and 'Paper' for each CustomerId within each Year.

But my requirement is to capture the count of transition from Online to Paper (and) Paper to Online for each CustomerId within each year.

I.e, For example
For CustomerId = 3 within 2013, since that customer has changed from
- PAPER to ONLINE one time, and then
- ONLINE to PAPER another time, and then
- PAPER to ONLINE once again,
the output should show 2 under column "PaperToOnline" and 1 under column "OnlineToPaper" (as shown in the picture).



Thanks!

Hi,

This should help.

Regards
  David

with customer_ordered ( CustomerID, SubscriptionType, RowNo )
as (
	select 
		c.CustomerId
		, c.SubscriptionType
		, rank() over( partition by c.CustomerID order by c.SubscriptionChangeDate ) as RowNo
	from dbo.Customer c
)

select a.CustomerID, a.SubscriptionType, b.SubscriptionType
from customer_ordered a
left outer join customer_ordered b
	on b.CustomerID = a.CustomerID
	and b.RowNo = a.RowNo + 1
where
	b.CustomerID is not null
;

Select allOpen in new window

Three quick questions -
Which version of SQL server you are using ?
Is the query from David resolve your issue ?
Can I assume that type will be changed during consecutive entries ?

Posting a draft query, May be helpful to start.

select CustomerId , 
       Yr , 
       Sum ( Online ) PaperToOnline , 
       Sum(Paper) OnlineToPaper
from 
(   Select	 c.CustomerID,  YEAR(SubscriptionChangeDate) Yr , 
			 count(Case when SubscriptionType = 'Online' and SubscriptionChangeDate <> StartDate then 1 
						else null end  )  as Online, 
			 count(Case when SubscriptionType = 'Paper' and SubscriptionChangeDate <> StartDate then 1 
						else null end  )   as Paper
	FROM	Customer  c  
	inner join ( /*Remove starting transaction type */ 
				 select CustomerID,
				        min(SubscriptionChangeDate) StartDate 
				 from Customer 
				 group by CustomerID )FirstTran
	on  c.CustomerID = FirstTran.CustomerID 
	group by c.CustomerID , 
			 YEAR(SubscriptionChangeDate) , 
			 SubscriptionType  
) AS FinTab
Group By CustomerId , Yr 
order by CustomerId

Select allOpen in new window

Sorry for the delay in response.

Below is my answer to the 3 question:

Question 1:
Which version of SQL server you are using ?  
SQL SERVER 2008 R2


Question 2:
Is the query from David resolve your issue ?
It works, but does not cover the case when the Subscription type is the same for a customer across records with diff SubscriptionChangeDate.

Eg: please insert the following values to the table mentioned in my earlier post.

INSERT INTO dbo.Customer 
VALUES
(4, '01-JUN-2013','Paper'),
(4, '01-SEP-2013','Paper'),
(4, '05-NOv-2013','Paper'),
(4, '28-Dec-2013','Paper');

(5, '01-JUN-2013','Paper'),
(5, '01-SEP-2013','Paper'),
(5, '05-NOv-2013','Online'),
(5, '28-Dec-2013','Online');

Select allOpen in new window

The above CTE sql, returns the following for Customer Id = 4
CustomerId      Yr          PaperToOnline      OnlineToPaper
4                     2013          0                               3
5                     2013          2                               1


But it should actually return:
CustomerId      Yr          PaperToOnline      OnlineToPaper
4                     2013          0                               0
5                     2013          1                               0



as all the subscription change for CustomerId = 4 is between the same Subscription type (I.e, PAPER to PAPER), and for CustomerId = 5, there is only subscription change that is between different Subscription types (Paper to Online).


Question 3:
Can I assume that type will be changed during consecutive entries ?
Not necessary. The consecutive entries may have the same type (for eg, if they changed from ONLINE to subscription to ONLINE Subscription) or different type  (For eg, If they changed from ONLINE to PAPER/PAPER to ONLINE)

Both the solutions work prefectly.

    Thanks you David and Anoo!