price_tl as (

select

date_trunc('day', hour) as date,

case

when blockchain = 'polygon'

and token_address = '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' then '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

when blockchain = 'arbitrum'

and token_address = '0x82af49447d8a07e3bd95bd0d56f35241523fbab1' then '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

when blockchain = 'optimism'

and token_address = '0x4200000000000000000000000000000000000006' then '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

when blockchain = 'bsc'

and token_address = '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c' then '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

when blockchain = 'avalanche'

and token_address = '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee'

else token_address

end as address,

CASE

when token_address = '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'optimism'

when token_address = '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'arbitrum'

else blockchain

end as block_chain,

avg(price) as price

from

crosschain.core.fact_hourly_prices

WHERE

IS_IMPUTED = 'false'

and PROVIDER = 'coingecko'

group by

1,

2,

3

),

arb_con as (

SELECT

ADDRESS,