How to spread revenue over variable time period (Monthly, Yearly etc) with SQL?

Question

the background for this is that I want to calculate MRR (Monthly Recurring Revenue) for a subscription-based business, where the revenue is recorded when a sale is made, along with the time-period of the contract, represented by the effective end date.

The task is then to split the revenue into the term of the contract, and aggregate multiple contracts so that revenue per month can be shown. It would be a plus to also show the churn, i.e. the $ loss that occurs when a contract comes to an end and is not renewed.

The format of the data is as such (ignore the billing period):

╔══════════════════════════════════╦══════════════════════════╦══════════════════════════╦════════════════╦═══════╗
║            account_id            ║        start_date        ║    effective_end_date    ║ billing_period ║ price ║
╠══════════════════════════════════╬══════════════════════════╬══════════════════════════╬════════════════╬═══════╣
║ 2c92a0fd5286d62801528d6578230fa7 ║ 2015-10-01T00:00:00.000Z ║ 2017-10-15T00:00:00.000Z ║ Annual         ║ 1440  ║
║ 2c92a0fd5286d62801528d6578230fa8 ║ 2015-10-01T00:00:00.000Z ║ 2016-10-15T00:00:00.000Z ║ Annual         ║ 3500  ║
║ 2c92a0fd5286d62801528d6578230fa9 ║ 2015-10-01T00:00:00.000Z ║ 2015-12-31T00:00:00.000Z ║ Annual         ║ 700  ║
╚══════════════════════════════════╩══════════════════════════╩══════════════════════════╩════════════════╩═══════╝

The desired result would show the following:

2c92a0fd5286d62801528d6578230fa7 spreads $1440 over 24 Months, from Oct 2015 to Oct 2017.

2c92a0fd5286d62801528d6578230fa8 spreads $3500 over 12 Months, from Oct 2015 to Oct 2016.

2c92a0fd5286d62801528d6578230fa9 spreads $700 over 3 Months, from Oct 2015 to Dec 2015.

I realize that I need to use a date table to cross join, because otherwise all dates would not be represented. I can do this with a CTE. But I'm more flummoxed by how to divide the revenue. Any help would be appreciated!

This is where I've gotten to so far:

SELECT account_id, date_trunc('month',effective_start_date) as start_date, effective_end_date, mrr as price, 
EXTRACT(YEAR FROM age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + EXTRACT(month from age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)) as contract_length_months, 
mrr/NULLIF(EXTRACT(YEAR FROM age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + EXTRACT(month from age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)),0) as divided_price
FROM "public"."zuora_rate_plan_charge" where mrr <> 0 and mrr is not null 
order by date_trunc('month',effective_start_date)

Result:

╔══════════════════════════════════╦══════════════════════════╦══════════════════════════╦═══════╦════════════════════════╦═══════════════╗
║            account_id            ║        start_date        ║    effective_end_date    ║ price ║ contract_length_months ║ divided_price ║
╠══════════════════════════════════╬══════════════════════════╬══════════════════════════╬═══════╬════════════════════════╬═══════════════╣
║ 2c92a0fd5286d62801528d6578230fa7 ║ 2015-10-01T00:00:00.000Z ║ 2017-10-15T00:00:00.000Z ║  1440 ║                     24 ║            60 ║
╚══════════════════════════════════╩══════════════════════════╩══════════════════════════╩═══════╩════════════════════════╩═══════════════╝

Desired Result:

╔════════╦════════════════╗
║ Month  ║      MRR       ║
╠════════╬════════════════╣
║ Oct 15 ║ 585            ║
║ Nov 15 ║ 585            ║
║ Dec 15 ║ 585            ║
║ Jan 16 ║ 351.6666666667 ║
╚════════╩════════════════╝

Answer 1

You can use generate_series() to get the months and then some arithmetic to get the data. To get all the months for one customer:

select t.*, price / count(*) over (partition by account_id) as monthy
from (select t.*,
             generate_series(start_date, end_date, interval '1 month') as yyyymm
      from t
     ) t;

Then, you can aggregate this if you want the amount per month:

select yyyymm, sum(monthly)
from (select t.*,
             price / count(*) over (partition by account_id) as monthly
      from (select t.*,
                   generate_series(start_date, end_date, interval '1 month') as yyyymm
            from t
           ) t
      ) t
group by yyyymm
order by yyyymm;