Postgres show count 0 for non-matching rows for 1 table
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 &lt; 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 &gt; 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.