SQL Studio

WITH monthly_revenue AS (SELECT date_trunc('month', created_at) AS month, account_id, SUM(amount_usd) AS total_revenue, COUNT(DISTINCT order_id) AS total_orders
FROM orders
WHERE status = 'completed' AND created_at >= '2025-01-01'
GROUP BY 1, 2)
SELECT month, account_id, total_revenue, total_orders, AVG(total_revenue) OVER(PARTITION BY account_id
ORDER BY month rows BETWEEN 3 preceding AND current row) AS moving_avg_revenue
FROM monthly_revenue
ORDER BY month desc, total_revenue desc
LIMIT 50;