Get count of susbcribers for each month in current year even if count is 0

Question

I need to get the count of new subscribers each month of the current year.

DB Structure: Subscriber(subscriber_id, create_timestamp, ...)

Expected result:

date       | count
-----------+------
2021-01-01 | 3
2021-02-01 | 12
2021-03-01 | 0
2021-04-01 | 8
2021-05-01 | 0

I wrote the following query:

SELECT
       DATE_TRUNC('month',create_timestamp)
         AS  create_timestamp,
       COUNT(subscriber_id) AS count
FROM subscriber
GROUP BY DATE_TRUNC('month',create_timestamp);

Which works but does not include months where the count is 0. It's only returning the ones that are existing in the table. Like:

"2021-09-01 00:00:00"   3
"2021-08-01 00:00:00"   9

Use generate_series for year wise month then left join your query. — Rahul Biswas, Sep 25 '21 at 18:20
@RahulBiswas can you give an example? I am not that much experienced with SQL — rabih, Sep 25 '21 at 18:29
`count of new subscribers` - define "new". Can the same `subscriber_id` subscribe repeatedly? Can there be NULL values? Your query makes it seem like we just count all rows ... — Erwin Brandstetter, Sep 25 '21 at 23:07

Rahul Biswas · Answer 1 · 2021-09-25T19:19:37.133

0

First subquery is used for retrieving year wise each month row then LEFT JOIN with another subquery which is used to retrieve month wise total_count. COALESCE() is used for replacing NULL value to 0.

-- PostgreSQL (v11)
SELECT t.cdate
     , COALESCE(p.total_count, 0) total_count
FROM (select generate_series('2021-01-01'::timestamp, '2021-12-15', '1 month') as cdate) t
LEFT JOIN (SELECT DATE_TRUNC('month',create_timestamp) create_timestamp
                , SUM(subscriber_id) total_count
           FROM subscriber
           GROUP BY DATE_TRUNC('month',create_timestamp)) p
       ON t.cdate = p.create_timestamp

Please check from url https://dbfiddle.uk/?rdbms=postgres_11&fiddle=20dcf6c1784ed0d9c5772f2487bcc221

edited Sep 25 '21 at 19:19

answered Sep 25 '21 at 18:51

Rahul Biswas

3,207
2
10
20

It does not work if I change it to match my table's data – rabih Sep 25 '21 at 19:01
@rabih I've provided a pseudocode. Please check the link where you'll get an idea. Month wise subscription count query must be placed in LEFT JOIN ( your query ) and use exact column name. – Rahul Biswas Sep 25 '21 at 19:06
Please check now. Your given query is placed here. – Rahul Biswas Sep 25 '21 at 19:20

score 0 · Answer 2 · answered Sep 25 '21 at 23:20

get the count of new subscribers each month of the current year

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);

db<>fiddle here

That's assuming every row is a "new subscriber". So count(*) is simplest and fastest.

See:

Get count of susbcribers for each month in current year even if count is 0

2 Answers2