How to get this postgres group by number of days, including days from 0

Question

I have this Postgres query to select all users in the last 30 days and group by number.

@users = User.where('created_at >= ?', last_30_day).count(:order => 'DATE(created_at) ASC', :group => ["DATE(created_at)"])

The problem is that it returns like this

2014-01-15     6
2014-01-13     2
2014-01-09     1

And I would like to get days with cero results, as well as how

2014-01-15     6
2014-01-14     0
2014-01-13     2
2014-01-12     0
2014-01-11     0
2014-01-10     0
2014-01-09     1

Any idea how to do this?

Answer 1

To state the answer to @mu_is_too_short, you can do this with the generate_seriesfollowing:

SELECT dt.series, COUNT(u.created_at)
FROM users u
RIGHT OUTER JOIN (
    SELECT 
    GENERATE_SERIES( (NOW() - INTERVAL '30 day')::date, NOW()::date, '1 day')::date
    AS series
) AS dt 
on DATE_TRUNC('month', u.created_at) = dt.series
GROUP BY dt.series
ORDER BY dt.series DESC

What gives:

  series   | count
------------+-------
 2014-01-08 |     0
 2014-01-07 |     0
 2014-01-06 |     0
 2014-01-05 |     0
 2014-01-04 |     0
 2014-01-03 |     0
 2014-01-02 |     0
 2014-01-01 |     1
 2013-12-31 |     0

At the same time, the ActiveRecord syntax for this is pretty ugly, it's best to write it in pure SQL above and just get the original results. Sort of:

sql = <<-SQL
    SELECT dt.series, COUNT(u.created_at)
    FROM users u
    RIGHT OUTER JOIN (
        SELECT 
        GENERATE_SERIES( (NOW() - INTERVAL '30 day')::date, NOW()::date, '1 day')::date
        AS series
    ) AS dt 
    on DATE_TRUNC('month', u.created_at) = dt.series
    GROUP BY dt.series
    ORDER BY dt.series DESC
SQL

records = User.connection.select_all(sql)
records.each do |record|
  puts record['series']
end

<<-SQL , , .

Answer 2

, , :

zeros = 30.downto(0)
          .map { |n| n.days.ago.to_date.iso8601 }
          .each_with_object({}) { |d, h| h[d] = 0 }

:

@users = zeros.merge(User.where(...).count(...))

, OUTER JOIN , generate_series:

select dt.d, count(*)
from (
    select current_date - 30 + i
    from generate_series(0, 30) as gs(i)
) as dt(d)
left join user u on date(u.created_at) = dt.d

AR SQL , , .