Skip to content
Advertisement

Use PostreSQL 9.3 to calculate Monthly Recurring Revenue (MRR), by customer

I’ve found a few answers that can get me close here and here, but not exactly what I’m looking for.

Here’s the sample data set

start end custid amt
2021-02-01 2022-01-31 1 10
2021-03-01 2021-03-31 2 20

The ideal output would generate the series for each customer like the table below

month custid amt
2021-02-28 1 10
2021-03-31 1 10
2021-04-30 1 10
2021-05-31 1 10
2021-06-30 1 10
2021-07-31 1 10
2021-08-31 1 10
2021-09-30 1 10
2021-10-31 1 10
2021-11-30 1 10
2021-12-31 1 10
2022-01-31 1 10
2021-03-31 2 20

I’m assuming this will require the generate_series function, but quite sure how to proceed.

Edit: spelled out MRR in the title so we’re all on the same page.

Edit 2: The code block below seems to work, but still very interested in other approaches that might be more efficient

SQL Fiddle

Advertisement

Answer

Use generate_series to create a table of months:

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement