PostgreSQL query to count/group by day and display days with no data

I'll try to provide an answer that includes some explanation. I'll start with the smallest building block and work up.

If you run a query like this:

SELECT series.number FROM generate_series(0, 9) AS series(number)

You get output like this:

 number 
--------
      0
      1
      2
      3
      4
      5
      6
      7
      8
      9
(10 rows)

This can be turned into dates like this:

SELECT CURRENT_DATE + sequential_dates.date AS date
  FROM generate_series(0, 9) AS sequential_dates(date)

Which will give output like this:

    date    
------------
 2019-09-29
 2019-09-30
 2019-10-01
 2019-10-02
 2019-10-03
 2019-10-04
 2019-10-05
 2019-10-06
 2019-10-07
 2019-10-08
(10 rows)

Then you can do a query like this (for example), joining the original query as a subquery against whatever table you're ultimately interested in:

   SELECT sequential_dates.date,
          COUNT(calendar_items.*) AS calendar_item_count
     FROM (SELECT CURRENT_DATE + sequential_dates.date AS date
             FROM generate_series(0, 9) AS sequential_dates(date)) sequential_dates
LEFT JOIN calendar_items ON calendar_items.starts_at::date = sequential_dates.date
 GROUP BY sequential_dates.date

Which will give output like this:

    date    | calendar_item_count 
------------+---------------------
 2019-09-29 |                   1
 2019-09-30 |                   8
 2019-10-01 |                  15
 2019-10-02 |                  11
 2019-10-03 |                   1
 2019-10-04 |                  12
 2019-10-05 |                   0
 2019-10-06 |                   0
 2019-10-07 |                  27
 2019-10-08 |                  24

You just need a left outer join instead of an inner join:

SELECT d.date, count(se.id)
  FROM 
    (
    SELECT to_char(date_trunc('day', (current_date - offs)), 'YYYY-MM-DD') AS date 
       FROM generate_series(0, 365, 1) AS offs
    ) d
  LEFT OUTER JOIN sharer_emailshare se 
    ON d.date = to_char(date_trunc('day', se.created), 'YYYY-MM-DD')
  GROUP BY d.date;

Extending Gordon Linoff's helpful answer, I would suggest a couple of improvements such as:

• Use ::date instead of date_trunc('day', ...)
• Join on a date type rather than a character type (it's cleaner).
• Use specific date ranges so they're easier to change later. In this case I select a year before the most recent entry in the table - something that couldn't have been done easily with the other query.
• Compute the totals for an arbitrary subquery (using a CTE). You just have to cast the column of interest to the date type and call it date_column.
• Include a column for cumulative total. (Why not?)

Here's my query:

WITH dates_table AS (
    SELECT created::date AS date_column FROM sharer_emailshare WHERE showroom_id=5
)
SELECT series_table.date, COUNT(dates_table.date_column), SUM(COUNT(dates_table.date_column)) OVER (ORDER BY series_table.date) FROM (
    SELECT (last_date - b.offs) AS date
        FROM (
            SELECT GENERATE_SERIES(0, last_date - first_date, 1) AS offs, last_date from (
                 SELECT MAX(date_column) AS last_date, (MAX(date_column) - '1 year'::interval)::date AS first_date FROM dates_table
            ) AS a
        ) AS b
) AS series_table
LEFT OUTER JOIN dates_table
    ON (series_table.date = dates_table.date_column)
GROUP BY series_table.date
ORDER BY series_table.date

I tested the query, and it produces the same results, plus the column for cumulative total.