SELECT

date,

total_count,

address_count,

volume,

AVG(total_count) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as average_moving_txns,

AVG(address_count) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as average_moving_address,

AVG(volume) OVER (ORDER BY date ROWS BETWEEN 49 PRECEDING AND CURRENT ROW) as average_moving_volume

FROM (SELECT

date_trunc('day', block_timestamp) as date,

COUNT(DISTINCT from_address) as address_count,

COUNT(DISTINCT tx_hash) as total_count,

sum(tx_fee * price) as volume

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 date)

ORDER BY date

SELECT *

FROM final_data

WHERE date = date_trunc('day', CURRENT_DATE - 1 )