prices as (

select

date(block_timestamp) as date,

replace(feed_name, ' / USD') as symbol,

median(coalesce(latest_answer_adj, latest_answer_unadj / pow(10,8))) as price --using median cause there will be some nulls / zeroes

from ethereum.chainlink.ez_oracle_feeds

where feed_category = 'Cryptocurrency (USD pairs)'

and feed_name in ('ETH / USD', 'AVAX / USD', 'MATIC / USD', 'BNB / USD')

group by 1,2

order by 1

),

ethereum_volume as (

select

date(l.block_timestamp) as date,

'ethereum' as chain,

price,

sum(tx_fee) as total_fee,

count(distinct t.tx_hash) as tx_count,

total_fee * price as total_fee_usd,

total_fee_usd / tx_count as fee_usd_per_tx

from ethereum.core.fact_event_logs l

join ethereum.core.fact_transactions t

on t.tx_hash = l.tx_hash

join prices p

on p.date = date(l.block_timestamp)

where origin_to_address in (

'0xc578cbaf5a411dfa9f0d227f97dadaa4074ad062', -- 2.0

'0x841ce48f9446c8e281d3f1444cb859b4a6d0738c', -- 1

'0x5427fefa711eff984124bfbb1ab6fbf5e3da1820' -- v2

)

and tx_status = 'SUCCESS'

and event_name = 'Send' -- take only source chain

and p.symbol = 'ETH'

group by 1,2,3