Nic V. Nic V. - 4 months ago 10
SQL Question

Get month over month increase in usage for each customer

I have the following table:

DECLARE @MyTable TABLE (
CustomerName nvarchar(max),
[Date] date,
[Service] nvarchar(max),
UniqueUsersForService int
)

INSERT INTO @MyTable VALUES
('CompanyA', '2016-07-14', 'Service1', 100),
('CompanyA', '2016-07-15', 'Service1', 110),
('CompanyA', '2016-07-16', 'Service1', 120),
('CompanyA', '2016-07-14', 'Service2', 200),
('CompanyA', '2016-07-15', 'Service2', 220),
('CompanyA', '2016-07-16', 'Service2', 500),
('CompanyB', '2016-07-14', 'Service1', 10000),
('CompanyB', '2016-07-15', 'Service1', 10500),
('CompanyB', '2016-07-16', 'Service1', 11000),
('CompanyB', '2016-07-14', 'Service2', 200),
('CompanyB', '2016-07-15', 'Service2', 300),
('CompanyB', '2016-07-16', 'Service2', 300)


Basically it's a list that shows how many people used each service for each company. For instance, in
CopmanyA
, on the
14th of July
, 100 unique users used
Service1
. The actual table contains thousands of customers and dates going back to the 1st of Jan 2015.

I've been researching online for a way to be able to calculate the usage increase month-over-month for each service per customer. What I managed to do so far: I grouped the dates by months.

For instance the date
7/14/2016
is
201607
(the 7th month of 2016) and selected the maximum usage for the respective month. So now I need to figure out how to calculate the difference in usage between June and July for example.

To somehow subtract the usage of June from the one in July. And so on for each month. The end goal is to identify the customers that had the biggest increase in usage - percentagewise. I want to be able to look at the data and say CompanyA was using 100 licenses in March and in April he jumped to 1000. That's a 1000% increase.

I apologize for the way I phrased the question, I am very new to SQL and coding in general and I thank you in advance for any help I might get.

Answer

If you are using SQL Server 2012 (and up) you can use LAG function:

;WITH cte AS (
SELECT  CustomerName,
        LEFT(REPLACE(CONVERT(nvarchar(10),[Date],120),'-',''),6) as [month],
        [Service],
        MAX(UniqueUsersForService) as MaxUniqueUsersForService
FROM @MyTable
GROUP BY CustomerName,
        LEFT(REPLACE(CONVERT(nvarchar(10),[Date],120),'-',''),6),
        [Service]
)

SELECT  *, 
        LAG(MaxUniqueUsersForService,1,NULL) OVER (PARTITION BY CustomerName, [Service] ORDER BY [month]) as prevUniqueUsersForService
FROM cte
ORDER BY CustomerName, [month], [Service]

In SQL Server 2008:

;WITH cte AS (
SELECT  CustomerName,
        LEFT(REPLACE(CONVERT(nvarchar(10),[Date],120),'-',''),6) as [month],
        [Service],
        MAX(UniqueUsersForService) as MaxUniqueUsersForService
FROM @MyTable
GROUP BY CustomerName,
        LEFT(REPLACE(CONVERT(nvarchar(10),[Date],120),'-',''),6),
        [Service]
)

SELECT c.*,
        p.MaxUniqueUsersForService as prevUniqueUsersForService
FROM cte c
OUTER APPLY (SELECT TOP 1 * FROM cte WHERE CustomerName = c.CustomerName AND [Service] = c.[Service] and [month] < c.[month]) as p