with curr_eth_price as (

select symbol,

avg(price) as price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

group by 1, hour

qualify row_number() over (partition by symbol order by hour desc) = 1

select

-- block_timestamp::date as date,

count(distinct tx_hash) as tx_count,

-- sum(tx_count) over (order by date) as cumu_tx,

count(distinct to_address) as to_address_count,

count(distinct origin_from_address) as from_address_count

-- sum(raw_amount_precise::integer/1e18) as eth_amount,

-- sum(eth_amount) over (order by date) as cumu_amount,

-- sum(raw_amount_precise::integer/1e18*price) as usd_amount,

-- sum(usd_amount) over (order by date) as cumu_usd

from base.core.fact_token_transfers

join curr_eth_price

where

contract_address = '0x4200000000000000000000000000000000000006'

and block_timestamp::date >= '2023-07-26'