PostgreSQL – Show Count 0 for Non-Matching Rows

postgresql

Let's say I have this schema.

create table foo_access (
    id         serial primary key,
    user_id    integer not null,
    created_at timestamptz not null
);

-- This data set uses user_id=0 to mean null. _shrug_
insert into foo_access (user_id, created_at) values (0, '2020-03-20T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-19T00:00:00Z');
insert into foo_access (user_id, created_at) values (1, '2020-03-18T00:00:00Z');
insert into foo_access (user_id, created_at) values (2, '2020-03-18T00:00:00Z');

I want to see how many different users have accessed foo at least once a day, for the last 5 days.

I have a query like this.

select
  date_trunc('day', created_at) as period,
  count(distinct user_id) as n
from foo_access
where user_id > 0 and
  created_at >= now() - interval '5 day'
group by period

Which gives me a table like this.

         period         | n
------------------------+---
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(2 rows)

Almost what I want, but not quite.

Is there a way to also show the days where no one accessed foo? I'd like the table to look more like this.

         period         | n
------------------------+---
 2020-03-15T00:00:00+00 | 0
 2020-03-16T00:00:00+00 | 0
 2020-03-17T00:00:00+00 | 0
 2020-03-18T00:00:00+00 | 2
 2020-03-19T00:00:00+00 | 1
(5 rows)

SQL Fiddle: http://sqlfiddle.com/#!17/ece05/2/0

This question is somewhat similar to How to count matching values and print 0 for non-matching value in PostgreSQL?, except I'm not joining two tables. I only have 1 table.

Best Answer

WITH RECURSIVE
cte AS ( SELECT date_trunc('day', now() - interval '5 day') AS period
         UNION ALL
         SELECT period + INTERVAL '1 day'
         FROM cte
         WHERE period < date_trunc('day', now()) )
select
  cte.period,
  count(distinct foo_access.user_id) as n
from cte
LEFT JOIN foo_access on cte.period = date_trunc('day', foo_access.created_at)
                    AND foo_access.user_id > 0
group by cte.period;

fiddle

I want to see how many different users have accessed foo at least once a day, for the last 5 days.

In my query you'll get 6-day statistic (except while quering at the midnight). You may fix it if needed.

Related Question