from external.tokenflow_starknet.decoded_traces

where

CHAIN_ID = 'mainnet'

FUNCTION = 'constructor'

and

CLASS_HASH = '0x03131fa018d520a037686ce3efddeab8f28895662f019ca3ca18a626650f7d1e'),

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'

end as addy,

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

DECIMALS , avg(PRICE) as pr

from crosschain.core.ez_hourly_prices

where HOUR::date >= current_date - 730

and SYMBOL in ('USDT','USDC','DAI','WBTC','WETH','wstETH')

and BLOCKCHAIN = 'ethereum'

GROUP by 1,2,3,4),

eth_to as (SELECT PARAMETERS[0]:value::string as sender,PARAMETERS[1]:value::string as receiver, PARAMETERS[2]:value[0]:value::string/pow(10,DECIMALS ) as volume, volume*pr as usd ,token , tx_hash, TIMESTAMP::date as date

from external.tokenflow_starknet.decoded_events

join prices on datex = timestamp::date and contract= addy

where --tx_hash = '0x06a159fd4bca6203f043d2ed9641e2b9984db5e48380b302f8c09c53a51ece18'

CHAIN_ID = 'mainnet'

and name = 'Transfer'

and CONTRACT = '0x049d36570d4e46f48e99674bd3fcc84644ddd6b96f7c741b1562b82f9e004dc7'

and PARAMETERS[1]:value != '0x1176a1bd84444c89232ec27754698e5d2e7e1a7f1539f12027f28b23ec9f3d8'),