prices as (

select

hour,

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

price

from ethereum.core.fact_hourly_token_prices

where symbol in (

'WETH','BICO','USDT','USDC'

)

),

withdraws as (

select

block_timestamp,

tx_hash,

'0x'|| substr(topics[1],27) as token_withdrawn,

case token_withdrawn

when '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' then 'USDC'

when '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' then 'USDT'

when '0xa68ec98d7ca870cf1dd0b00ebbb7c4bf60a8e74d' then 'BICO'

when '0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee' then 'ETH'

end as token,

case token_withdrawn

when '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' then 6

when '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' then 6

else 18

end as decimals,

ethereum.public.udf_hex_to_int(substr(topics[2],3))/pow(10,decimals) as target_amount,

ethereum.public.udf_hex_to_int(substr(topics[3],3))/pow(10,decimals) as actual_amount,

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

'0x'|| substr(segmented_data[0],25) as receiver,

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

case chainId

when '1' then 'ETH'

when '10' then 'OPT'

when '56' then 'BSC'