Postgres: How can you select last 12 months only, and return total count of each month, even with null/empty months?

Question

I'm using Postgres, and my dataset is an orders table. I'm trying to learn how to show the total amount of orders per month, per user (assuming there's only one user in this case).

My dataset:

id, user_id, total, created_at

My current query:

SELECT
       DATE_TRUNC('month',created_at)
         AS  total_orders,
       COUNT(id) AS count
FROM orders
WHERE id = 1
GROUP BY DATE_TRUNC('month',created_at);

This returns:

Total orders | count
2021-01-01 00:00:00, 1

However, here's the tricky part (to me at least) - i am using this to generate some graphs. Specifically, the last 12 months. So i would like the query to return the last 12 months, and include 0 for when no orders were created. So this is expected output:

Total orders | count
2021-04-01 00:00:00, 0
2021-03-01 00:00:00, 0
2021-02-01 00:00:00, 0
2021-01-01 00:00:00, 1
2020-12-01 00:00:00, 0
2020-11-01 00:00:00, 0
2020-10-01 00:00:00, 0
2020-09-01 00:00:00, 0
2020-08-01 00:00:00, 0
2020-07-01 00:00:00, 0
2020-06-01 00:00:00, 0
2020-05-01 00:00:00, 0
2020-04-01 00:00:00, 0

How can I accomplish this? The ultimate goal is to be super lightweight so that it's not resource intensive and fast.

Answer 1

One option is to create a date dimensions table which you can JOIN on for various date dependent goals such as this one. The date dimensions table typically has useful fields describing a specific date in time such as DateOf, DayOf, MonthOf, YearOf, DayOfWeek, IsHoliday, IsWeekend, etc. But for this specific case, you'd just simply use the MonthOf and YearOf fields in a LEFT JOIN from the date dimensions table like so:

SELECT
       CONCAT(DD.YearOf, '-', DD.MonthOf, '-01 00:00:00') AS  total_orders,
       COALESCE(COUNT(O.id), 0) AS count
FROM DateDimensions DD
LEFT JOIN orders O
    DD.DateOf = O.created_at
WHERE O.id = 1
GROUP BY DD.YearOf, DD.MonthOf

Alternatively I believe you can generate a date series with the generate_series function in PostgreSQL too, but I'm not super versed on how to do so. This DBA.StackExchange answer has a lot of good information on generating date series.

Answer 2

Found this solution that works great!

select date(d) as day, count(sales.id) 
from generate_series(
  current_date - interval '30 day', 
  current_date, 
  '1 day'
) d 
left join sales on date(sales.created_at) = d 
group by day order by day;

https://www.sisense.com/blog/use-generate-series-to-get-continuous-results/

This article answers the question for any future lurkers!