select

hour,

token_address,

symbol,

decimals,

avg (price) as usd_amount

from crosschain.price.ez_prices_hourly

group by 1,2,3,4)

date_trunc('day', block_timestamp) as date,

COUNT(DISTINCT tx_hash) AS transaction_count,

COUNT(DISTINCT sender) AS unique_sender_count,

SUM(amount*usd_amount) AS transaction_volume,

AVG(transaction_count) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA_7_transactions,

AVG(transaction_count) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS MA_30_transactions,

AVG(transaction_volume) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA_7_volume,

AVG(transaction_volume) OVER (ORDER BY date ROWS BETWEEN 29 PRECEDING AND CURRENT ROW) AS MA_30_volume,

AVG(unique_sender_count) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS MA_7_traders,

SUM(transaction_count) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_tx_count,

SUM(transaction_volume) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_tx_volume

FROM

axelar.defi.ez_bridge_satellite t1 join price t2 on date_trunc('hour', t1.block_timestamp) = t2.hour and t1.token_address = t2.token_address

where

date >= '2024-01-01'

group by date