I have a table that has 2 columns:
Date: all dates from 1/1/16-12/31/16
ID: unique identifier that is a text field. Specific IDs may or may not occur across multiple days.
I want to count a rolling 7 day tally of distinct IDs for each day. Here is SQL to do it for a single day:
Select '2017-01-07' as week_end_date, count(distinct(id)) as ids
where date between '2016-01-01' and '2016-01-07'
Counting distinct ids is tricky. If performance is not an issue, you can do:
Select week_start_date + interval '6 day' as week_end_date, count(distinct id) as ids from table join generate_series('2016-01-01'::date, '2017-01-01'::date, interval '1 day') as g(week_start_date) where date between week_start_date and week_start_date + interval '6 day';