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
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;