Friday, January 22, 2016

Calculating Customer's Latency

There were times when I was asked to retrieve the latency of the customers in my work. At first, I was confused about how to retrieve this. After some thought, I find a solution, which is actually pretty simple, and the SQL usage is widely available. Unfortunately, when I tried searching it online, the SQL technique is seldom associated with latency problem in retail industry.

This is an illustration of what we want to achieve.
Given members and their transaction date. We want to find the average latency of members. To get the average latency, we would need to get the difference in days, between member's 1st and 2nd visit, 2nd and 3rd visit, 3rd and 4th visit, etc. The average of these differences will be the average latency of the members.


To do this on SQL, we can rank the member's transaction based on the TransactDate, and then self-join to the same set of records, but shift the ranking by one order. This way, we will get the difference of days between a transaction and the subsequent transaction.

Here are the SQL queries to prepare the data:

--Create temp table
CREATE TABLE #Transact
(
 MemberID NVARCHAR(100),
 RecieptNo NVARCHAR(100),
 TransactDate DATE
)

--Insert dummy data
INSERT INTO #Transact VALUES ('M001', 'R001', '2015/11/20')
INSERT INTO #Transact VALUES ('M001', 'R002', '2015/12/30')
INSERT INTO #Transact VALUES ('M001', 'R003', '2016/01/05')

INSERT INTO #Transact VALUES ('M002', 'R004', '2015/09/12')
INSERT INTO #Transact VALUES ('M002', 'R005', '2015/09/15')
INSERT INTO #Transact VALUES ('M002', 'R006', '2015/09/25')
INSERT INTO #Transact VALUES ('M002', 'R007', '2015/10/30')

Here are the SQL queries to get the Latency per member:

--Store the ranked transaction into temp table. Alternately, use common table expression
SELECT MemberID, TransactDate, RankNo = ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY TransactDate)
INTO #1
FROM #Transact

--Get the average latency per member
SELECT MemberID, Latency = AVG(CONVERT(NUMERIC(18,2), Latency))
FROM ( SELECT a.MemberID,
   Latency = DATEDIFF(DAY, a.TransactDate, b.TransactDate)
  FROM #1 a
  INNER JOIN #1 b
   ON b.MemberID = a.MemberID
   AND b.RankNo = (a.RankNo + 1) 
) aa GROUP BY aa.MemberID

That's all folks!

Share:

You may be intersted in

Related Posts

Updating Table Containing Xml Column via LinkedServer

If you are trying to update a table containing XML column via Linked Server in SQL Server, and you are not able to, you are not alone. There...

About Me

My photo
Is an ordinary man, with a little knowledge to share and high dreams to achieve. I'd be glad if I can help others, 'coz the only thing for the triumph of evil is for a good man to do nothing.

About Blog

You can find a lot of debugging and deploying problems while developing applications in .NET and Visual Basic here. There are also some querying tips in SQL and typical source codes which might be useful shared here.

Popular Posts

Blogroll

Followers

Leave a Message