Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

Number of times customer changed subscription

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
sath350163
Asked:
sath350163
  • 4
  • 3
  • 3
2 Solutions
 
David ToddSenior DBACommented:
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
 
Anoo S PillaiCommented:
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
 
sath350163Author Commented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
David ToddSenior DBACommented:
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
 
Anoo S PillaiCommented:
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
 
Anoo S PillaiCommented:
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
 
sath350163Author Commented:
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
 
Anoo S PillaiCommented:
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
 
David ToddSenior DBACommented:
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
 
sath350163Author Commented:
Both the solutions work prefectly.

    Thanks you David and Anoo!
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now