SQL calculate complex MRR

user_19492180 0
Viewed 38

I have been struggling for a few days now on a way to calculate MRR for the table as follows :

ID ActualEndDate CreatedAt PlanType Price
ABC 2023-06-09 2022-06-10 Annual 110
CFR 2022-08-11 2022-06-12 Mensual 12
CFR 2022-08-15 2022-07-15 Mensual 12

ActualEndDate : when the subscription is stopping at the moment

CreatedAt : when the subscription has started

PanType : type of subscription

Price : price of subscription

Monthly price : price of subscription per month

In my case i would like to calculate the MRR as follows :

The annual price should be counted each month (with the monthly price) from the CreatedAt to the ActualEndDate. The mensual price should be counted on the month it’s CreatedAt and then till the ActualEndDate.

I guess this is doable with CTE, but i did not find a good way to do it for now.

In my case i woul like a result as follows :

Month MRR
2022-06 23
2022-07 35
2022-08 35

Thank you all !

common-table-expression sum date sql
1 Answers

The monthly price column was missing, so I infered the prices

I tested in MySQL, with this table:

subscriptions:

id ActualEndDate CreatedAt PlanType Price MonthlyPrice
ABC 2023-06-09 2022-06-10 Annual 110 11
CFR 2022-08-11 2022-06-12 Mensual 12 12
CFR 2022-08-15 2022-07-15 Mensual 12 12

Check if this suits your needs, I created a Period subtable with the last 6 months, current month and the next six months.

SELECT DATE_FORMAT(Period.`Month`, '%Y-%m') `Month`,
       COALESCE(SUM(MonthlyPrice), 0) MRR
FROM (
              SELECT DATE_FORMAT(NOW() - INTERVAL 6 MONTH,'%Y-%m-01') AS `Month`
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 5 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 4 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 3 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 2 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() - INTERVAL 1 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 0 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 1 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 2 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 3 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 4 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 5 MONTH,'%Y-%m-01')
    UNION ALL SELECT DATE_FORMAT(NOW() + INTERVAL 6 MONTH,'%Y-%m-01')) AS Period
LEFT JOIN subscriptions ON Period.`Month` 
                           BETWEEN DATE_FORMAT(subscriptions.CreatedAt,'%Y-%m-01') 
                               AND LAST_DAY(subscriptions.ActualEndDate) 
GROUP BY Period.`Month`
user_192235860
Related