with

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'

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 >= '2023-09-01'

and SYMBOL = 'WETH' --in ('USDT','USDC','DAI','WBTC','WETH','wstETH','rETH','LORDS')

and BLOCKCHAIN = 'ethereum'

GROUP by 1,2,3,4) ,

actions as

(SELECT TIMESTAMP::date as date,CALLER as wl, TX_HASH as txs, case

when contract = '0x04c9f960760ded4663ae9f79fccc3fa6b816cec009951e5216d760362e7ff1e8' then 'Layerwap'

when contract = '0x043e8f14fa00a6275ef875de1ae19db9f7eccb35ffb753bef1158cc097939c26' then 'Avnu'

when contract = '0x05d1a2e0510c25cbf3c9a2be8fc528344573cb1ab770091deca2561f75f4e285' then 'mySwap'

when contract = '0x025c602164d202593bbba07714f9f66567f2679e4362e7dc3039451f12b13929' then 'Bitget'

when contract = '0x001f99a7dab042386334980744c27bbf91154dfb6dcc9c887d82fbecbf55d1fd' then 'Owolto'

when contract = '0x05e4aa1f595b9f33390a0266633adc3158812b618dc1bf11678b58a36a68c85a' then 'Starknet.id'

when contract = '0x01007a1cc4c27162d244e56197ef10de791842534677718276bea2e0f804c489' then 'Brine.fi'

when contract = '0x0563898228a255c95822b256c480d27817cfcfeefa0cf20ed1be2bb48175e6d8' then 'Redstone'

when contract = '0x038f5e7efc9606e54e6d6ec8c16f007cafd1bb373b6f3e230f421123a8efd647' then 'Influence'

when contract = '0x016028e9358979b4a2bb447bcee4a1884cb9fb17ba127f257c2c039e7df91531' then 'ArgentX'

when contract = '0x01d8b1f125039eb3332a9e16b7e0c95f46fdabba92039fd26df6d05d53fedee7' then 'Unframed'

when contract = '0x045dc42889b6292c540de9def0341364bd60c2d8ccced459fac8b1bfc24fa1f5' then 'Orbiter'