prices AS (

SELECT

eth.hour::date AS day,

AVG(eth.price) AS price_eth,

AVG(btc.price) AS price_btc,

AVG(avax.price) AS price_avax,

AVG(link.price) AS price_link

FROM

ethereum.core.fact_hourly_token_prices eth

LEFT JOIN ethereum.core.fact_hourly_token_prices btc ON eth.hour::date = btc.hour::date

LEFT JOIN ethereum.core.fact_hourly_token_prices avax ON eth.hour::date = avax.hour::date

LEFT JOIN ethereum.core.fact_hourly_token_prices link ON eth.hour::date = link.hour::date

WHERE

eth.symbol = 'WETH'

AND btc.symbol = 'WBTC'

AND avax.symbol = 'WAVAX'

AND link.symbol = 'LINK'

GROUP BY

day

ORDER BY

day

)

CASE

WHEN contract_address = '0x5c0401e81bc07ca70fad469b451682c0d747ef1c' THEN 'AVAX'

WHEN contract_address = '0xf362fea9659cf036792c9cb02f8ff8198e21b4cb' THEN 'sAVAX'

WHEN contract_address = '0x89a415b3d20098e6a6c8f7a59001c67bd3129821' THEN 'BTC.b'

WHEN contract_address = '0xe194c4c5ac32a3c9ffdb358d9bfd523a0b6d1568' THEN 'WBTC.e'

WHEN contract_address = '0x872670ccae8c19557cc9443eff587d7086b8043a' THEN 'BUSD'

WHEN contract_address = '0x835866d37afb8cb8f8334dccdaf66cf01832ff5d' THEN 'DAI.e'

WHEN contract_address = '0x334ad834cd4481bb02d09615e7c11a00579a7909' THEN 'WETH.e'

WHEN contract_address = '0x4e9f683a27a6bdad3fc2764003759277e93696e6' THEN 'LINK.e'

WHEN contract_address = '0x35bd6aeda81a7e5fc7a7832490e71f757b0cd9ce' THEN 'QI'

WHEN contract_address = '0xb715808a78f6041e46d61cb123c9b4a27056ae9c' THEN 'USDC'

WHEN contract_address = '0xbeb5d47a3f720ec0a390d04b4d41ed7d9688bc7f' THEN 'USDC.e'