with hop_txs as (

select TX_HASH from ethereum.core.fact_event_logs

where contract_address in ('0xb8901acb165ed027e32754e0ffe830802919727f','0x3666f603cc164936c1b87e207f36beba4ac5f18a','0x3e4a3a4796d16c0cd582c382691998f7c06420b6','0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1','0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2')

), transfers as (

select

date_trunc('week',block_timestamp) as day,

amount_usd,

origin_from_address,

a.tx_hash

from

ethereum.core.ez_token_transfers a join hop_txs b on a.tx_hash=b.tx_hash

union all

select

date_trunc('week',block_timestamp) as day,

amount_usd,

origin_from_address,

a.tx_hash

from

ethereum.core.ez_eth_transfers a join hop_txs b on a.tx_hash=b.tx_hash

select

day,

sum(amount_usd) as volume_usd,

count(distinct origin_from_address) as user_count,

count(distinct tx_hash) as tx_count,

avg(volume_usd) over (order by day asc rows between 7 preceding and current row) as volume_ma,

avg(user_count) over (order by day asc rows between 7 preceding and current row) as users_ma,

avg(tx_count) over (order by day asc rows between 7 preceding and current row) as txe_ma

from

transfers

group by 1