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?