How to calculate revenue for each month?-CodePudding

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)
;

CodePudding user response：

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.

The fiddle

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)
;