-- forked from sim strk arb -bridge comp @ https://flipsidecrypto.xyz/edit/queries/bb409ba5-0a67-4a21-af9e-7a588ba41bb5

with accs as (SELECT DISTINCT CONCAT('0x', LTRIM(CONTRACT, '0x')) as addr, CONTRACT as addr0

from external.tokenflow_starknet.decoded_traces

where

CHAIN_ID = 'mainnet'

FUNCTION = 'constructor'

),

prices as (SELECT HOUR::date as datex,

case when symbol = 'USDT' then '0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8'

when symbol = 'USDC' then '0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8'

when symbol = 'DAI' then '0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3'

when symbol = 'WBTC' then '0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac'

when symbol = 'wstETH' then '0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2'

when symbol = 'WETH' then '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

when symbol = 'rETH' then '0x0319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610'

when symbol = 'LORDS' then '0x0124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49'

when symbol = 'LUSD' then '0x070a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac'

when symbol = 'UNI' then '0x049210ffc442172463f3177147c1aeaa36c51d152c1b0630f2364c300d4f48ee'

end as addy,

case when symbol = 'WETH' then 'ETH' else SYMBOL end as token,

DECIMALS , avg(PRICE) as pr

from crosschain.price.ez_hourly_token_prices

where HOUR::date >= current_date - 365

and TOKEN_ADDRESS in

('0x6b175474e89094c44da98b954eedeac495271d0f',

'0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2',

'0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0',

'0x5f98805a4e8be255a32880fdec7f6728c6568ba0',

'0xae78736cd615f374d3085123a210448e74fc6393',

'0x1f9840a85d5af5bf1d1762f925bdaddc4201f984',