sql - How to generate a date range + count earlier dates from another table in PostgreSQL? -

i have following table:

links:

created_at           active  2017-08-12 15:46:01  false 2017-08-13 15:46:01  true 2017-08-14 15:46:01  true 2017-08-15 15:46:01  false 

when given date range, have extract time series tells me how many active links created on date equal or smaller current (rolling) date.

output (for date range 2017-08-12 - 2017-08-17):

day          count 2017-08-12   0 (there 0 active links created on 2017-08-12 , earlier) 2017-08-13   1 (there 1 active link created on 2017-08-13 , earlier) 2017-08-14   2 (there 2 active links created on 2017-08-14 , earlier) 2017-08-15   2 ... 2017-08-16   2 2017-08-17   2 

i came following query generating dates:

select date_trunc('day', dd):: date generate_series     ( '2017-08-12'::timestamp      , '2017-08-17'::timestamp     , '1 day'::interval) dd 

but rolling counts confuse me , unsure how continue. can solved window function?

this should fastest:

select day::date      , sum(ct) on (order day) count   generate_series (timestamp '2017-08-12'                       , timestamp '2017-08-17'                       , interval  '1 day') day left   join  (    select date_trunc('day', created_at) day, count(*) ct      tbl     active -- fastest    group  1    ) t using (day) order  1; 

dbfiddle here

count() counts non-null rows, use count(active or null). fastest option counting exclude irrelevant rows where clause begin with. since adding days generate_series() anyway, best option.

compare:

• for absolute performance, sum faster or count?

since generate_series() returns timestamp (not date) use date_trunc() matching timestamps (very faster).