0

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.

0
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.

| improve this answer | |
  • 1
    I think it would be simpler to replace the recursive CTE with something like generate_series(date_trunc('day',now()-interval '5 days'),now(), interval '1 day') – jjanes Mar 20 at 14:16
  • @jjanes Maybe. But I do not prefer postgres, so always forget about the constructions specific for it. – Akina Mar 20 at 14:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.