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

),

bsc_stg as (

select date(a.block_timestamp) as date,

'bsc' as chain,

price,

sum(tx_fee) as total_fee,

count(distinct a.tx_hash) as tx_count,

total_fee * price as total_fee_usd,

total_fee_usd / tx_count as fee_usd_per_tx

from bsc.core.fact_token_transfers a

join bsc.core.fact_transactions t

on t.tx_hash = a.tx_hash

join prices p

on p.date = date(a.block_timestamp)

where a.origin_function_signature = '0x2e15238c' -- send

and a.to_address = '0x0000000000000000000000000000000000000000'

and p.symbol = 'BNB'

group by 1, 2, 3),

avalanche_stg as (

select date(a.block_timestamp) as date,

'avax' as chain,

price,