PostgreSQL - How to get month/year even if there are no records within that date?

Question

What I'm trying to do in this case is to get the ''most future'' record of a Bills table and get all the record prior 13 months from that last record, so what I've tried is something like this

    SELECT
          users.name,
          EXTRACT(month from priority_date) as month,
          EXTRACT(year from priority_date) as year,
          SUM("money_balance") as "money_balance"
    FROM bills
    JOIN users on users.id = bills.user_id
    WHERE priority_date >= (  SELECT
                                  DATE_TRUNC('month', MAX(debts.priority_date))
                                    FROM bills
                                    INNER JOIN users ON bills.property_id = users.id
                                    WHERE users.company_id = 15
                                    AND users.active = true
                                    AND bills.paid = false ) - interval '13 month'
    AND priority_date  <= (  SELECT
                                 MAX(bills.priority_date)
                                FROM bills
                                INNER JOIN users ON bills.property_id = users.id
                                WHERE users.community_id = 15
                                AND users.active = true
                                AND debts.paid = false )
   AND users.company_id = 15
   AND bills.paid = false
   AND users.active = true
   GROUP BY 1,2,3
   ORDER BY year, month

So for instance, lets say the most future date for a created bill is December 2022, this query will give me the info from November 2021 to December 2022 The data will give me something like

name	month	year	money_balance
Joshua..	11	2021	300
Joshua..	1	2022	111
Mark..	1	2022	200
...	...	...	...
John	12	2022	399

In the case of Joshua, because he had no bills to pay in December 2021, it doesn't return anything for that month/year.

Is it possible to return the months/year where there are no records for that month, for each user?

Something like

name	month	year	money_balance
Joshua..	11	2021	300
Joshua..	12	2021	0
Joshua..	1	2022	111
other users	....	...	...

Thank you so much!

Answer 1

We can use a CTE to create the list of months, using the maximum and minimum dates from bill, and then cross join it onto users to get a line for all users for all months. We then left join onto bills to populate the last column.
The problem with this approach is that we can end up with a lot of rows with no value.

create table bills(user_id int,priority_date date, money_balance int);
create table users(id int, name varchar(25));
insert into users values(1,'Joshua'),(2,'Mark'),(3,'John');
insert into bills values(1,'2021-11-01',300),(1,'2022-01-01',111),(2,'2022-01-01',200),(3,'2021-12-01',399);

;with months as
(SELECT to_char(generate_series(min(priority_date), max(priority_date), '1 month'), 'Mon-YY') AS "Mon-YY"
from bills)
SELECT
  u.name,
  "Mon-YY",
  --EXTRACT(month from "Mon-YY") as month,
  --EXTRACT(year from "Mon-YY") as year,
  SUM("money_balance") as "money_balance"
FROM months m
CROSS JOIN users u
LEFT JOIN bills b
ON u.id = b.user_id 
  AND to_char(priority_date,'Mon-YY') = m."Mon-YY"
GROUP BY 
  u.name,
  "Mon-YY"
ORDER BY "Mon-YY", u.name

name   | Mon-YY | money_balance
:----- | :----- | ------------:
John   | Dec-21 |           399
Joshua | Dec-21 |          null
Mark   | Dec-21 |          null
John   | Jan-22 |          null
Joshua | Jan-22 |           111
Mark   | Jan-22 |           200
John   | Nov-21 |          null
Joshua | Nov-21 |           300
Mark   | Nov-21 |          null

db<>fiddle here