Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE subscriber (

subscriber_id int

, create_timestamp timestamp

);

INSERT INTO subscriber VALUES

(1, date_trunc('year', LOCALTIMESTAMP))

, (2, date_trunc('year', LOCALTIMESTAMP) + interval '1 month')

, (3, date_trunc('year', LOCALTIMESTAMP) + interval '2 month')

, (31, date_trunc('year', LOCALTIMESTAMP) + interval '2 month')

, (32, date_trunc('year', LOCALTIMESTAMP) + interval '2 month')

, (4, date_trunc('year', LOCALTIMESTAMP) + interval '3 month')

, (6, date_trunc('year', LOCALTIMESTAMP) + interval '5 month')

, (11, date_trunc('year', LOCALTIMESTAMP) + interval '8 month')

;

8 rows affected

SELECT month::date, COALESCE(s.count, 0) AS count

FROM generate_series(date_trunc('year', LOCALTIMESTAMP)

, date_trunc('year', LOCALTIMESTAMP) + interval '11 month'

, interval '1 month') m(month)

LEFT JOIN (

SELECT date_trunc('month', create_timestamp) AS month

, count(*) AS count

FROM subscriber

GROUP BY 1

) s USING (month);