[revicon]

Weird, the example in the post (after changing table/field names for my database)

  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

Throws an error for me...

  ERROR:  syntax error at or near "SELECT"
  LINE 5: SELECT weeks.week,
          ^

[barsonme]

yeah, the comma after the "with" block shouldn't be there.

i.e.,

    ... weeks
    )
    SELECT weeks.week

[revicon]

makes sense. After removing it...

  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.

[combatentropy]

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
    ;

[cmaggard]

Remove the comma before the SELECT?