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

the background want calculate mrr (monthly recurring revenue) subscription-based business, revenue recorded when sale made, along time-period of contract, represented effective end date.

the task split revenue term of contract, , aggregate multiple contracts revenue per month can shown. plus show churn, i.e. $ loss occurs when contract comes end , not renewed.

the format of data such (ignore 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 show following:

2c92a0fd5286d62801528d6578230fa7 spreads $1440 on 24 months, oct 2015 oct 2017.

2c92a0fd5286d62801528d6578230fa8 spreads $3500 on 12 months, oct 2015 oct 2016.

2c92a0fd5286d62801528d6578230fa9 spreads $700 on 3 months, oct 2015 dec 2015.

i realize need use date table cross join, because otherwise dates not represented. can cte. i'm more flummoxed how divide revenue. appreciated!

this i've gotten far:

select account_id, date_trunc('month',effective_start_date) start_date, effective_end_date, mrr price,  extract(year age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + extract(month age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)) contract_length_months,  mrr/nullif(extract(year age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date))*12 + extract(month age(date_trunc('month',effective_end_date)::date,date_trunc('month', effective_start_date)::date)),0) divided_price "public"."zuora_rate_plan_charge" mrr <> 0 , mrr not null  order 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 ║ ╚════════╩════════════════╝ 

you can use generate_series() months , arithmetic data. months 1 customer:

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

then, can aggregate if want amount per month:

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