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

CREATE TABLE invoices
(
    "start" date,
    "end" date,
    custid int,
    amt float
);

INSERT INTO invoices
VALUES ('2021-02-01', '2022-01-31', 1, 10),
       ('2021-03-01', '2021-03-31', 2, 20)
;  
       
select * from invoices

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

CREATE TABLE invoices
(
    "start" date,
    "end" date,
    custid int,
    amt float
);

INSERT INTO invoices
VALUES ('2021-02-01', '2022-01-31', 1, 10),
       ('2021-03-01', '2021-03-31', 2, 20)
;  
       
WITH cal AS ( -- calender table
        SELECT generate_series('2021-02-01'::date, '2022-01-31'::date , '1 month'::interval)::date dt
        )
SELECT
        a.custid, a.amt
        ,a.start
        ,a.end
        ,c.dt
FROM cal c
CROSS JOIN invoices a
where a.start <= c.dt and a.end >=c.dt
order by a.custid, c.dt
;

SQL Fiddle

Answer

Use generate_series to create a table of months:

SELECT (s.d + '1 month')::date - 1 AS month,
       i.custid,
       i.amt
FROM invoices AS i
   CROSS JOIN LATERAL generate_series(
                         date_trunc('month', i.start::timestamp),
                         i.end::timestamp,
                         '1 month'
                      ) AS s(d)
ORDER BY i.custid, s.d;

   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
(13 rows)