txs as (

select date_trunc('hour',block_timestamp) as date_hour , tx_hash, origin_from_address, origin_to_address, decoded_log,

case when decoded_log:to='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' then decoded_log:from

when decoded_log:from='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' then decoded_log:to

end as user,

contract_address::string as token_address,

case when token_address='0x9702230a8ea53601f5cd2dc00fdbc13d4df4a8c7' then 'USDt'

when token_address='0x093783055f9047c2bff99c4e414501f8a147bc69' then 'ALOT'

when token_address='0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' then 'USDC'

when token_address='0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' then 'ETH'

when token_address='0x152b9d0fdc40c096757f570a51e494bd4b943e50' then 'BTC.b'

else token_address end as token,

case when token_address='0x9702230a8ea53601f5cd2dc00fdbc13d4df4a8c7' then decoded_log:value*pow(10,-6)

when token_address='0x093783055f9047c2bff99c4e414501f8a147bc69' then decoded_log:value*pow(10,-18)

when token_address='0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' then decoded_log:value*pow(10,-6)

when token_address='0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab' then decoded_log:value*pow(10,-18)

when token_address='0x152b9d0fdc40c096757f570a51e494bd4b943e50' then decoded_log:value*pow(10,-8)

else decoded_log:value*pow(10,-18) end as token_amount,

case when decoded_log:to='0x1fd108cf42asssssssssss59c635bd4703b8dbc8a741ff834be' then 'Deposit'

when decoded_log:from='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' then 'Withdraw'

end as event_type

from avalanche.core.ez_decoded_event_logs

where event_name='Transfer' AND token_address in ('0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e','0x093783055f9047c2bff99c4e414501f8a147bc69','0x152b9d0fdc40c096757f570a51e494bd4b943e50','0x49d5c2bdffac6ce2bfdb6640f4f80f226bc10bab','0x9702230a8ea53601f5cd2dc00fdbc13d4df4a8c7') AND

(decoded_log:to='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be' OR decoded_log:from='0x1fd108cf42a59c635bd4703b8dbc8a741ff834be')

),

px_tb as (

select token_address,

case when token_address='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' then 'AVAX'

-- token_address='0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'

when token_address='0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e' then 'USDC'