tokenList AS (

SELECT tokenSymbol, l1Address, starknetAddressWith0s,starknetAddress, decimals

FROM (

VALUES('ETH', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7','0x49d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7', 18)

, ('BTC', '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599', '0x03fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', '0x3fe2b97c1fd336e750087d68b9b867997fd64a2661ff3ca5a7c771641e8e7ac', 8)

, ('DAI', '0x6b175474e89094c44da98b954eedeac495271d0f', '0x00da114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', '0xda114221cb83fa859dbdb4c44beeaa0bb37c7537ad5ae66fe5e0efd20e6eb3', 18)

, ('USDT', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0x068f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', '0x68f5c6a61780768455de69077e07e89787839bf8166decfbf92b645209c0fb8', 6)

, ('USDC', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0x053c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', '0x53c91253bc9682c04929ca02ed00b3e423f6710d2ee7e0d5ebb06f3ecf368a8', 6)

, ('wstETH', '0x7f39c581f595b53c5cb19bd0b3f8da6c935e2ca0', '0x042b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', '0x42b8f0484674ca266ac5d08e4ac6a3fe65bd3129795def2dca5c34ecc5f96d2', 18)

, ('LORDS', '0x686f2404e77ab0d9070a46cdfb0b7fecdd2318b0', '0x0124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', '0x124aeb495b947201f5fac96fd1138e326ad86195b98df6dec9009158a533b49', 18)

, ('rETH', '0xae78736cd615f374d3085123a210448e74fc6393', '0x0319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610', '0x319111a5037cbec2b3e638cc34a3474e2d2608299f3e62866e9cc683208c610', 18 )

, ('LUSD', '0x5f98805a4e8be255a32880fdec7f6728c6568ba0', '0x070a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac', '0x70a76fd48ca0ef910631754d77dd822147fe98a569b826ec85e3c33fde586ac', 18)

) AS t(tokenSymbol, l1Address, starknetAddressWith0s, starknetAddress, decimals)

, prices AS (

SELECT

DATE_TRUNC('HOUR', HOUR) AS HOUR

, TOKEN_ADDRESS

, AVG(price) AS price

FROM crosschain.price.fact_hourly_token_prices

WHERE BLOCKCHAIN = 'ethereum'

AND HOUR > '2023-11-20'

AND TOKEN_ADDRESS IN (

SELECT l1Address FROM tokenList

GROUP BY 1,2

, pairList AS (

SELECT

ev.PARAMETERS[0]:value AS pairAddress

, CASE len(ev.PARAMETERS[0]:value)

WHEN 65 THEN CONCAT ('0x0', SUBSTR((ev.PARAMETERS[0]:value), 3, 64))