Solved

Number of times customer changed subscription

Posted on 2014-03-09
10
198 Views
Last Modified: 2014-03-18
Hello,

Table Structure
Please see the above table structure.

Basically I want to group by Customer Id and Year, and then get the count of number of times a customer changed from Paper to Online Subscription, as well as Online to Paper Subscription.

How do I achieve this?

Thanks!
0
Comment
Question by:sath350163
  • 4
  • 3
  • 3
10 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 
LVL 7

Expert Comment

by:Anoo S Pillai
Comment Utility
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 

Open 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.
0
 

Author Comment

by:sath350163
Comment Utility
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');

Open 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!
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
;

Open in new window

0
 
LVL 7

Expert Comment

by:Anoo S Pillai
Comment Utility
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 ?
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 7

Expert Comment

by:Anoo S Pillai
Comment Utility
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

Open in new window

0
 

Author Comment

by:sath350163
Comment Utility
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');

Open 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)
0
 
LVL 7

Assisted Solution

by:Anoo S Pillai
Anoo S Pillai earned 250 total points
Comment Utility
Probably the following code may be helpful. Try and revert ( without delay :)  )

;with CTE_Customer 
as ( select c.CustomerId, c.SubscriptionType, c.SubscriptionChangeDate, year(SubscriptionChangeDate) Yr,
            rank() over( partition by c.CustomerID ,  
                                      year(SubscriptionChangeDate) 
                         order by c.SubscriptionChangeDate ) as RowNum 
	 from dbo.Customer c ), 
CTE_SubChanges 
as ( select	c1.CustomerID, c1.SubscriptionType SourceSubType, c1.Yr, 
			c2.SubscriptionType TargetSubType,c2.SubscriptionChangeDate, 
			case when c1.SubscriptionType <> c2.SubscriptionType then 1 else 0 end as ChangeCount
	 from	CTE_Customer c1
	 left join CTE_Customer c2
	 on		c1.CustomerID = c2.CustomerID
	 and	c1.RowNum + 1 = c2.RowNum )
select	CustomerID,  
		Yr , 
		SUM ( Case when TargetSubType = 'Online' then ChangeCount else 0 end  ) as PaperToOnline, 
		SUM ( Case when TargetSubType = 'Paper' then ChangeCount else 0 end  )  as OnlineToPaper
from  CTE_SubChanges FT
group by FT.CustomerID , Yr
order by FT.CustomerID , Yr

Open in new window

0
 
LVL 35

Accepted Solution

by:
David Todd earned 250 total points
Comment Utility
Hi,

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

select
	z.CustomerID
	, year( z.SubscriptionChangeDate ) as yr
	, isnull( sum( z.PaperToOnline ), 0 ) as TotalPaperToOnline
	, isnull( sum( z.OnlineToPaper ), 0 ) as TotalOnlineToPaper
from (
	select 
		a.CustomerID
		, b.SubscriptionChangeDate
		, a.SubscriptionType as OldType
		, b.SubscriptionType as NewType
		, case when a.SubscriptionType = 'Paper' and b.SubscriptionType = 'Online' then 1 end as PaperToOnline
		, case when a.SubscriptionType = 'Online' and b.SubscriptionType = 'Paper' then 1 end as OnlineToPaper
	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
	) z
group by
	z.CustomerID
	, year( z.SubscriptionChangeDate )
order by
	z.CustomerID
	, year( z.SubscriptionChangeDate )
;

Open in new window


HTH
  David
0
 

Author Comment

by:sath350163
Comment Utility
Both the solutions work prefectly.

    Thanks you David and Anoo!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now