CODEWITHSUNDEEP
sql-server
Tasos
Tasos

Reputation: 7577

Find the Monthly Recurring Revenue in SQL Server

I try to calculate the Monthly Recurring Revenue in a SQL Server database for subscription orders.

Getting some details from Baremetrics about MRR

How is it calculated?

Say you have 10 customers each paying you $10 a month. Here's how you would calculate MRR.

All non-monthly plans are normalized to monthly. So if a customer is paying you $120 on an annual plan, we add $10 to MRR for the next 12 months. Neat!

So, what I have done until now, is to take care of all the raw data and include upgrades, downgrades, refunds and cancelations that could affect the MRR metric and prepare the following table:

DateCreated OrderID     Price   Months
2013-03-05  1           49.9500 12
2013-04-01  2           5.9500  1
2013-04-08  3           7.1100  12
2013-05-15  4           42.9500 3
2013-06-12  5           13.9850 6
2013-06-27  6           7.1100  6
2013-07-29  7           21.9250 3

For the MRR now. I expect to have two columns, first with the date of starting month and year (01-03-2013 for example) and the second with the total revenue based on MRR definition.

For example, the OrderID 1 should be divided by 12 and add the result to the current and next 11 months. OrderID 2 should be all on that month since the subscription plan is for 1 month and go on.

Any idea how I can proceed with the rest of it?

Example result:

Month       MRR
01-03-2013  4.1625
01-04-2013  4.1625 + 5.95 + 0.5925
01-05-2013  4.1625 + 0.5925 + 14.31 
01-06-2013  4.1625 + 0.5925 + 14.31 + 2.33 + 1.185
01-07-2013  4.1625 + 0.5925 + 14.31 + 2.33 + 1.185
01-08-2013  4.1625 + 0.5925 + 2.33 + 1.185 + 7.30
01-09-2013  4.1625 + 0.5925 + 2.33 + 1.185 + 7.30
01-10-2013  4.1625 + 0.5925 + 2.33 + 1.185 + 7.30
01-11-2013  4.1625 + 0.5925 + 2.33 + 1.185
01-12-2013  4.1625 + 0.5925
01-01-2014  4.1625 + 0.5925
01-02-2014  4.1625 + 0.5925
01-03-2014  0.5925

Prices to add per month:

49.95/12 = 4.1625
5.95/1 = 5.95
7.11/12 = 0.5925
42.95/3 = 14.31
13.985/6 = 2.33
7.11/6 = 1.185
21.925/3 = 7.30

I left the operations in the MRR column on the result, to make it clear. Obviously, the result should be the sum of them.

Create the input table:

CREATE TABLE orders
(
    DateCreated datetime,
    OrderID int,
    Price float,
    Months int
);

INSERT INTO orders 
VALUES (2013-03-05, 1, 49.9500, 12),
       (2013-04-01, 2, 5.9500, 1),
       (2013-04-08, 3, 7.1100, 12),
       (2013-05-15, 4, 42.9500, 3),
       (2013-06-12, 5, 13.9850, 6),
       (2013-06-27, 6, 7.1100, 6),
       (2013-07-29, 7, 21.92500, 3)

Upvotes: 1

Views: 1821

Answers (1)

Cato
Cato

Reputation: 3701

I used a recursive CTE to try and get the full months range you can ever need, but I adjusted to the 1st of the month Then I joined based on whether or not the order is applicable to that month, allowing for the fact that it remains current for n-1 further months (where n is your 'Months') - i then collected all the data in JOINER (which you could also select * from to see it) then I aggregated JOINER into a month by month summation.

I adjusted all dates to the first of the month to work with

WITH MTHS AS (SELECT dateADD(day, -DAY(MIN(datecreated)) + 1, MIN(datecreated)) MD, (SELECT MAX(dateadd(month,o2.months, o2.datecreated)) FROM ORDERS o2) AS MXD FROM Orders 
                UNION ALL
                SELECT DATEADD(month,1,MD), MTHS.MXD  FROM MTHS WHERE DATEADD(month,1,MD) < MTHS.mxd),
    JOINER AS
            (
                select MD, ord.price / ord.months mlyprice from MTHS  
                                    JOIN ORDERS ord ON dateadd(day, -day(ord.datecreated) + 1, ord.datecreated) 
                                    BETWEEN 
                                            dateadd(month,1-ord.months,MTHS.md ) 
                                            AND  
                                            MTHS.md 
            )
    SELECT J.MD,SUM(mlyprice)  FROM JOINER J GROUP BY J.MD;

I think it looks promising 

    CREATE TABLE orders
(
    DateCreated datetime,
    OrderID int,
    Price float,
    Months int
);

INSERT INTO orders 
VALUES ('2013-03-05', 1, 49.9500, 12),
       ('2013-04-01', 2, 5.9500, 1),
       ('2013-04-08', 3, 7.1100, 12),
       ('2013-05-15', 4, 42.9500, 3),
       ('2013-06-12', 5, 13.9850, 6),
       ('2013-06-27', 6, 7.1100, 6),
       ('2013-07-29', 7, 21.92500, 3)


    ;WITH MTHS AS (SELECT dateADD(day, -DAY(MIN(datecreated)) + 1, MIN(datecreated)) MD, (SELECT MAX(dateadd(month,o2.months, o2.datecreated)) FROM ORDERS o2) AS MXD FROM Orders 
                    UNION ALL
                    SELECT DATEADD(month,1,MD), MTHS.MXD  FROM MTHS WHERE DATEADD(month,1,MD) < MTHS.mxd),
        JOINER AS
                (
                    select MD, ord.price / ord.months mlyprice from MTHS  
                                        JOIN ORDERS ord ON dateadd(day, -day(ord.datecreated) + 1, ord.datecreated) 
                                        BETWEEN 
                                                dateadd(month,1-ord.months,MTHS.md ) 
                                                AND  
                                                MTHS.md 
                )
        SELECT J.MD,SUM(mlyprice)  FROM JOINER J GROUP BY J.MD

Upvotes: 1

Related Questions