time_series_fun.sql

WITH weeks AS (
  SELECT generate_series('2008-12-29'::date, now(), '7 days'::interval)::date AS week
)
SELECT
  j.id AS category_id,
  weeks.week,
  COALESCE(SUM(t.amount), 0) AS weekly_spend,
  COALESCE(MIN(t.amount), 0) AS minimum_spend,
  COALESCE(MAX(t.amount), 0) AS maximum_spend,
  COUNT(t.amount) AS transaction_count,
  SUM(COALESCE(SUM(t.amount), 0::numeric)) OVER (ORDER BY j.id, weeks.week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS rolling_four_week_spend,
  AVG(COALESCE(SUM(t.amount), 0::numeric)) OVER (ORDER BY j.id, weeks.week ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS four_week_sma,
  AVG(COALESCE(SUM(t.amount), 0::numeric)) OVER (ORDER BY j.id, weeks.week ROWS BETWEEN 12 PRECEDING AND CURRENT ROW) AS thirteen_week_sma
FROM categories j
LEFT JOIN weeks ON 1 = 1
LEFT JOIN transactions t ON t.occurred_on >= weeks.week AND t.occurred_on < (weeks.week + '6 days'::interval) AND t.category_id = j.id AND t.status = 0
GROUP BY j.id, weeks.week
ORDER BY j.id, weeks.week;