CODEWITHSUNDEEP
sqlpostgresql
Mark
Mark

Reputation: 172

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:

1/7/16: 

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?

Upvotes: 0

Views: 1014

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270613

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';

Upvotes: 1

Related Questions