unparsed_data as (

select

block_timestamp,

tx_hash,

topics,

-- data,

regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data

from ethereum.core.fact_event_logs

where contract_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'

and origin_function_signature = '0x49228978'

and block_timestamp >= '2022-01-01'

),

parsed_data as (

select

block_timestamp,

tx_hash,

ethereum.public.udf_hex_to_int(topics[1]) as deposit_Id,

'0x'|| substr(topics[3],27) as depositor,

'0x'|| substr(topics[2],27) as origin_Token,

symbol,

ethereum.public.udf_hex_to_int(segmented_data[0]) as amount,

amount/pow(10,decimals) as asset_amount,

asset_amount*price as usd_value,

ethereum.public.udf_hex_to_int(segmented_data[2]) as destination_ChainId,

case ethereum.public.udf_hex_to_int(segmented_data[2])

when 10 then 'optimism'

when 137 then 'polygon'

when 42161 then 'arbitrum'

when 288 then 'boba'

else null

end as destination_chain

from unparsed_data b join ethereum_core.fact_hourly_token_prices p

on date_trunc(hour,block_timestamp) = p.hour and '0x'|| substr(topics[2],27) = p.token_address