WITH daily_data AS (

SELECT

date_trunc('day', block_timestamp) as day,

COUNT(DISTINCT tx_hash) as transactions,

COUNT(DISTINCT from_address) as users,

sum(tx_fee * price) as tx_fee_usd

FROM optimism.core.fact_transactions

LEFT OUTER JOIN (

SELECT

hour,

price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol LIKE 'WETH'

) on hour = date_trunc('hour', block_timestamp)

WHERE block_timestamp > CURRENT_DATE - 365

GROUP BY 1

day,

transactions,

users,

tx_fee_usd,

AVG(transactions) OVER (ORDER BY day ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as moving_avg_transactions,

AVG(users) OVER (ORDER BY day ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as moving_avg_users,

AVG(tx_fee_usd) OVER (ORDER BY day ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as moving_avg_tx_fee_usd

FROM daily_data

ORDER BY day;