Mahilan
Reputation: 173
How to calculate revenue for each month?
The data provided is for a recurring revenue (per month) and the column definitions are below:
- Customer ID - Account Field (not unique)
- Contract start date – Opp Start Date
- Contract end date – OPP contract end date
- Contract value – MRR(expected revenue per month)
| Opp Start Date | OPP contract end date | Account Field | MRR(expected revenue per month) |
|---|---|---|---|
| 1/2/2013 | 1/2/2015 | 50e55 | 195.00 |
| 1/2/2013 | 1/2/2014 | 4ee75 | 50.00 |
| 1/2/2013 | 1/2/2014 | 4f031 | 75.00 |
| 1/2/2013 | 1/2/2016 | 4c3b2 | 133.00 |
| 1/2/2013 | 1/2/2016 | 49ec8 | 132.00 |
| 1/3/2013 | 1/3/2014 | 49fc8 | 59.00 |
| 1/4/2013 | 1/4/2015 | 49wc8 | 87.00 |
| 12/27/2013 | 12/27/2014 | 50bf7 | 190.00 |
| 12/27/2013 | 12/27/2014 | 59bb8 | 179.00 |
| 12/27/2013 | 12/27/2014 | 49ec8 | 147.00 |
Specifically, over a rolling 12 month period I would like to allocate revenue in each month to the following categories:
- Up-sell – an overall increase in revenue from existing customers spending more than they did 12 months ago (includes new/expired contracts of existing customers)
- Down-sell – a overall decrease in revenue from existing customers spending less than they did 12 months ago (includes new/expired contracts of existing customers)
- Churn – a decrease in revenue from customers leaving (i.e. they were a customer 12 months ago, but no longer are a customer; i.e. their last contracts have expired)
- New customers – an increase in revenue from new customers (i.e. those who weren’t customers 12 months ago; based on the first new contracts of customers.)
How can I achieve this in SQL?
Expected result (proposed), given the above data:
+---------+---------+--------+----------+---------+--------+
| Month | MRR | upsell | downsell | newsell | churn |
+---------+---------+--------+----------+---------+--------+
| 2013-01 | 731.00 | NULL | NULL | 731.00 | NULL | (195+50+75+133+132+59+87)
| 2013-02 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-03 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-04 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-05 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-06 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-07 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-08 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-09 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-10 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-11 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-12 | 1247.00 | NULL | NULL | 1247.00 | NULL | (731+190+179+147)
| 2014-01 | 1247.00 | 147.00 | NULL | 369.00 | NULL | (Account='49ec8' added 147. The rest are new Account contracts. Year over year this month)
| 2014-02 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-03 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-04 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-05 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-06 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-07 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-08 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-09 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-10 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-11 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-12 | 1063.00 | NULL | NULL | NULL | 184.00 |
| 2015-01 | 547.00 | NULL | -147.00 | NULL | 553.00 |
| 2015-02 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-03 | 265.00 | NULL | -147.00 | NULL | 651.00 |
... etc.
Note: up-sell and down-sell cases require Accounts to be capable of having multiple associated Contracts (example: Account = '49ec8').
The test case:
CREATE TABLE contracts (
StartDate DATE
, EndDate DATE
, Account VARCHAR(20)
, MRR DECIMAL(8,2)
);
INSERT INTO contracts VALUES
('1/2/2013' , '1/2/2015' , '50e55', 195.00)
, ('1/2/2013' , '1/2/2014' , '4ee75', 50.00)
, ('1/2/2013' , '1/2/2014' , '4f031', 75.00)
, ('1/2/2013' , '1/2/2016' , '4c3b2', 133.00)
, ('1/2/2013' , '1/2/2016' , '49ec8', 132.00)
, ('1/3/2013' , '1/3/2014' , '49fc8', 59.00)
, ('1/4/2013' , '1/4/2015' , '49wc8', 87.00)
, ('12/27/2013' , '12/27/2014' , '50bf7', 190.00)
, ('12/27/2013' , '12/27/2014' , '49cc8', 179.00)
, ('12/27/2013' , '12/27/2014' , '49ec8', 147.00)
;
Upvotes: 2
Views: 3309
Answers (1)
Jon Armstrong
Reputation: 4694
Here's my attempt, which generated the partial results shown in the question. There are a number of ways to do this. I chose to do this mainly with window functions, and not outer joins. Either is usable.
| CTE Term | Description |
|---|---|
| expand | Recursively generate the monthly revenue rows for each contract |
| step0 | Sum the revenue per Account per month |
| step1 | Find the prior year (month) revenue per account |
| step1 | ... Also find the next year (month) revenue per account, used to add rows representing accounts with no further contracts |
| step2 | Combine Last Year MRR detail with rows representing accounts with no further contracts |
| step3 | With the above: calculate upsell, downsell, newsell, churn |
| Final expression | SUM and generate results per month |
WITH expand (StartDate, EndDate, Account, MRR, CurrDate) AS (
SELECT c.*, CAST(CONCAT(LEFT(StartDate,7), '-01') AS date) FROM contracts AS c UNION ALL
SELECT StartDate, EndDate, Account, MRR, DATEADD(month, 1, CurrDate) FROM expand
WHERE CurrDate < EndDate
)
, step0 AS (
SELECT Account, SUM(MRR) AS MRR, CurrDate, COUNT(*) AS n
FROM expand
GROUP BY Account, CurrDate
)
, step1 AS (
SELECT *
, LAG( MRR, 12) OVER (PARTITION BY Account ORDER BY CurrDate) AS LYMRR
, LEAD(MRR, 12) OVER (PARTITION BY Account ORDER BY CurrDate) AS MRR2
FROM step0
)
, step2 AS (
SELECT Account, MRR, CurrDate, n, LYMRR FROM step1 UNION ALL
SELECT Account, MRR2 AS MRR, DATEADD(year, 1, CurrDate) AS CurrDate, 0 AS n, MRR AS LYMRR
FROM step1
WHERE MRR2 IS NULL
)
, step3 AS (
SELECT Account, MRR, CurrDate, n, LYMRR
, CASE WHEN MRR > LYMRR THEN MRR - LYMRR END AS upsell
, CASE WHEN MRR < LYMRR THEN MRR - LYMRR END AS downsell
, CASE WHEN LYMRR IS NULL THEN MRR END AS newsell
, CASE WHEN MRR IS NULL THEN LYMRR END AS churn
FROM step2
)
SELECT LEFT(CurrDate, 7) AS Month
, SUM(MRR) AS MRR
, SUM(upsell) AS upsell
, SUM(downsell) AS downsell
, SUM(newsell) AS newsell
, SUM(churn) AS churn
FROM step3
GROUP BY CurrDate
ORDER BY CurrDate
;
The result:
+---------+---------+--------+----------+---------+--------+
| Month | MRR | upsell | downsell | newsell | churn |
+---------+---------+--------+----------+---------+--------+
| 2013-01 | 731.00 | NULL | NULL | 731.00 | NULL | (195+50+75+133+132+59+87)
| 2013-02 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-03 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-04 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-05 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-06 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-07 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-08 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-09 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-10 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-11 | 731.00 | NULL | NULL | 731.00 | NULL |
| 2013-12 | 1247.00 | NULL | NULL | 1247.00 | NULL | (731+190+179+147)
| 2014-01 | 1247.00 | 147.00 | NULL | 369.00 | NULL | (Account='49ec8' added 147. The rest are new Account contracts. Year over year this month)
| 2014-02 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 | (churn = 50+75+59 accounts with no further contracts)
| 2014-03 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-04 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-05 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-06 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-07 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-08 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-09 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-10 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-11 | 1063.00 | 147.00 | NULL | 369.00 | 184.00 |
| 2014-12 | 1063.00 | NULL | NULL | NULL | 184.00 |
| 2015-01 | 547.00 | NULL | -147.00 | NULL | 553.00 |
| 2015-02 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-03 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-04 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-05 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-06 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-07 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-08 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-09 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-10 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-11 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2015-12 | 265.00 | NULL | -147.00 | NULL | 651.00 |
| 2016-01 | 265.00 | NULL | NULL | NULL | 282.00 |
| 2016-02 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-03 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-04 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-05 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-06 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-07 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-08 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-09 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-10 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-11 | NULL | NULL | NULL | NULL | 265.00 |
| 2016-12 | NULL | NULL | NULL | NULL | 265.00 |
| 2017-01 | NULL | NULL | NULL | NULL | 265.00 |
+---------+---------+--------+----------+---------+--------+
The setup:
CREATE TABLE contracts (
StartDate DATE
, EndDate DATE
, Account VARCHAR(20)
, MRR DECIMAL(8,2)
);
INSERT INTO contracts VALUES
('1/2/2013' , '1/2/2015' , '50e55', 195.00)
, ('1/2/2013' , '1/2/2014' , '4ee75', 50.00)
, ('1/2/2013' , '1/2/2014' , '4f031', 75.00)
, ('1/2/2013' , '1/2/2016' , '4c3b2', 133.00)
, ('1/2/2013' , '1/2/2016' , '49ec8', 132.00)
, ('1/3/2013' , '1/3/2014' , '49fc8', 59.00)
, ('1/4/2013' , '1/4/2015' , '49wc8', 87.00)
, ('12/27/2013' , '12/27/2014' , '50bf7', 190.00)
, ('12/27/2013' , '12/27/2014' , '49cc8', 179.00)
, ('12/27/2013' , '12/27/2014' , '49ec8', 147.00)
;
Upvotes: 4
Related Questions
- SQL Server Query monthly totals
- Total sales per month
- problem of calculating month to date revenue for each department in sql
- Total amount of sales in a month SQL Server Management Studio
- Monthly sales as column
- SQL - Tracking Monthly Sales
- Find the Monthly Recurring Revenue in SQL Server
- How to get the sum of sales per month?
- Calculate percentage of revenue per month
- SQL Server get sales for every month