Skip to content

Instantly share code, notes, and snippets.

@joshuaclayton
Created September 1, 2020 01:21
Show Gist options
  • Save joshuaclayton/54af6b71fefddc966e85859cfcda7320 to your computer and use it in GitHub Desktop.
Save joshuaclayton/54af6b71fefddc966e85859cfcda7320 to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment