How to use generate_series to get the sum of values in a weekly interval

Question

I'm having trouble using generate_series in a weekly interval. I have two examples here, one is in a monthly interval and it is working as expected. It is returning each month and the sum of the facts.sends values. I'm trying to do the same exact thing in a weekly interval, but the values are not being added correctly.

Monthly interval (Working): https://www.db-fiddle.com/f/a9SbDBpa9SMGxM3bk8fMAD/0

Weekly interval (Not working): https://www.db-fiddle.com/f/tNxRbCxvgwswoaN7esDk5w/2

Answer 1

You should generate a series that starts on Monday.

WITH range_values AS (
  SELECT date_trunc('week', min(fact_date)) as minval,
         date_trunc('week', max(fact_date)) as maxval
  FROM facts),

week_range AS (
  SELECT generate_series(date_trunc('week', '2022-05-01'::date), now(), '1 week') as week
  FROM range_values
),

grouped_facts AS (
  SELECT date_trunc('week', fact_date) as week,
    sends
  FROM facts
  WHERE
    fact_date >= '2022-05-20'
)

SELECT week_range.week,
       COALESCE(sum(sends)::integer, 0) AS total_sends
FROM week_range
LEFT OUTER JOIN grouped_facts on week_range.week = grouped_facts.week
GROUP BY 1
ORDER BY 1;

DB Fiddle.