SELECT
_day,
active_users,
AVG(active_users) OVER (ORDER BY _day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS avg_active_users_last_30_days
FROM (
SELECT
DATE_TRUNC('day', BLOCK_TIMESTAMP) AS _day,
COUNT(DISTINCT FROM_ADDRESS) AS active_users
FROM
polygon.core.fact_transactions
GROUP BY
_day
) subquery
ORDER BY _day;