with weeks as (
select week as week
from generate_series('2017-01-01'::date, now()::date, '1 week'::interval) weeks
)
SELECT weeks.week,
count(*)
FROM weeks,
test_results
WHERE
test_results.date_created > weeks.week
AND
test_results.date_created <= weeks.week - '1 week'::interval
it throws...
ERROR: column "week" does not exist
LINE 2: select week as week
^
I would move this to the post's own "replies" section, but it doesn't have one.
This should work. I'm sorry, I conformed it to my own style (lowercase and different indentation).
with weeks as (
select week
from generate_series(
'2017-01-01'::date,
now()::date,
'1 week'::interval
) week
)
select weeks.week, count(1)
from weeks, test_results
where
test_results.date_created > weeks.week and
test_results.date_created <= (weeks.week - '1 week'::interval)
group by week
;
