I have a dates table with a "calendar_date" field and I have been trying to utilize it for when I need to fill in missing dates when an id does not have a record for a particular day AND have the daily count = 0. I cannot seem to find the proper solution for it.

Result example:

enter image description here

        date_trunc('day',created_at) as activity_date,
        COALESCE(COUNT(DISTINCT user_id),0) AS daily_count
        FROM events e
        WHERE date(date_trunc('day',created_at)) <= CURRENT_DATE - INTERVAL '1 day' AND date(date_trunc('day',created_at)) >= CURRENT_DATE - INTERVAL '90 day'
        AND name = 'visited site'
        AND user_id != 0
        GROUP BY date_trunc('day',created_at), id

Any ideas/help would be appreciated!

  • Does this answer your question? Equal sized timeseries for each id
    – mustaccio
    Jul 27 '20 at 21:40
  • I tried this but the count distinct aspect of the query is still an issue
    – bdavis
    Jul 27 '20 at 22:23
  • Postgres or Redshift? Although they share some ancient roots, they are substantially different products now (In Postgres this would be simple using the generate_series() function) Jul 28 '20 at 5:38

In Postgres (which you explicitly tagged) you can do it like this. But I suspect you don't really use Postgres and the following is not supported by Redshift.

select d.id,
       count(distinct e.user_id) as daily_count
from (
  select distinct e1.id, g.dt::date as activity_date
  from events e1
    cross join generate_series(current_date - 90, current_date - 1, interval '1 day') as g(dt)
  where e1.activity_date >= current_date - 90
    and e1.activity_date <= current_date - 1
) d
  left join events e 
    on e.activity_date = d.activity_date
   and e.id = d.id
   and e.user_id <> 0
   and e.name = 'visited site'
group by d.id, d.activity_date

The query first creates a list of all IDs and dates in the desired interval. This is necessary because you also want to group by the id column. Then a left join is used to bring in the events and do the aggregation.

The WHERE clause in the derived table (inner query) means that only IDs are used, that have at least one event in the desired range. If you need all IDs regardless if there was an event or not in that time frame, you either need to pull them from a different table, or remove the WHERE clause of the inner query.

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.