select

hour::date as date,

symbol,

avg(price) as price

from

ethereum.core.fact_hourly_token_prices

where

token_address in ('0x418d75f65a02b3d53b2418fb8e1fe493759c7605', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')

and

hour::date >= CURRENT_DATE - 7

GROUP BY 1, 2

, data_bsc as (

select

block_timestamp::date as date,

avg(tx_fee*price) as fee_usd

from

bsc.core.fact_transactions a join data_price b on a.block_timestamp::date = b.date and b.symbol is null

where

block_timestamp::date >= CURRENT_DATE - 7

and

block_timestamp::date != CURRENT_DATE -- PREVENT NOT FULL TRANSACTIONS

group by 1

, data_arbitrum as (

select

block_timestamp::date as date,

avg((

case

when tokenflow_eth.hextoint(tx_json:receipt:effectiveGasPrice) = 0 then (tokenflow_eth.hextoint(tx_json:receipt:cumulativeGasUsed)*pow(10, -18))

else (tokenflow_eth.hextoint(tx_json:receipt:effectiveGasPrice)*pow(10,-18)*tokenflow_eth.hextoint(tx_json:receipt:gasUsed))

end

)*price) as fee_usd

from