Get month over month increase in usage for each customer

Question

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.

http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — , Jul 22 '16 at 08:40
@Nic V. check http://stackoverflow.com/questions/33570348/lead-and-lag-functionality-in-sql-server-2008 . — CiucaS, Jul 22 '16 at 08:45

score 0 · Accepted Answer · answered Jul 22 '16 at 09:12

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

Superb. It worked perfectly. Another quick one on the topic. I have another table that has 3 columns: CustomerName, Service, NumberofLincesesPurchased. How can I join these to make a percentage of the increase? Thanks a mill. — Nic V., Jul 22 '16 at 10:04
You can put above query in CTE and join it with the table you want to count percentage. — gofr1, Jul 22 '16 at 10:12

martennis · Answer 2 · 2016-07-22T09:44:35.793

If you're using SQL Server 2012 or newer, try this:

SELECT *
, CASE 
    WHEN uniqueUsersPrevMonth = 0 THEN uniqueUsersInMonth
    ELSE CAST(uniqueUsersInMonth - uniqueUsersPrevMonth as decimal) / uniqueUsersPrevMonth * 100
    END AS Increase
FROM (
    SELECT customer, service, DATEPART(MONTH, [date]) as [month]
    , SUM(uniqueUsers) AS uniqueUsersInMonth
    , LAG(SUM(uniqueUsers),1,0) OVER(PARTITION BY customer, service ORDER BY  DATEPART(MONTH, [date])) as uniqueUsersPrevMonth
    FROM @tbl AS t
    GROUP BY customer, service, DATEPART(MONTH, [date])
) AS t1
ORDER BY customer, service, [month]

Get month over month increase in usage for each customer

2 Answers2