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

Viewed 1767

I have the 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 a date range, I have to extract time series which tells me how many active links were created on a date equal or smaller than current (rolling) date.

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

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

I came up with the following query for generating dates:

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

But the rolling counts confuse me and am unsure how to continue. Can this be solved with a window function?

6 Answers
Related