Mark Mark -3 years ago 171
SQL Question

Rolling 7 Day Count Distinct in postgresql

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
from table
where date between '2016-01-01' and '2016-01-07'

Rather than run the above query 365 times, once for each date, is there any way to do this in one query?

Answer Source

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';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download