select

block_timestamp,

date_trunc('hour', block_timestamp) as hour,

tx_hash,

case

when DECODED_LOG ['tokenIn'] = '0x0000000000000000000000000000000000000000' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'

else DECODED_LOG ['tokenIn']

end as token_in,

case

when DECODED_LOG ['tokenOut'] = '0x0000000000000000000000000000000000000000' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'

else DECODED_LOG ['tokenOut']

end as token_out,

DECODED_LOG ['amountIn'] as amount_in,

DECODED_LOG ['amountOut'] as amount_out,

DECODED_LOG ['sender'] as user

from

avalanche.core.ez_decoded_event_logs

where

contract_address = lower('0x45a62b090df48243f12a21897e7ed91863e2c86b')

and ORIGIN_TO_ADDRESS = lower('0x45a62b090df48243f12a21897e7ed91863e2c86b') --only directly with router, ignore LO etc

and block_timestamp > TO_TIMESTAMP(1742947200) -- Wednesday, 26 March 2025 00:00:00

),

all_txs as (

select

block_timestamp,

tx_hash,

case

when concat('0x', substring(INPUT_DATA, 11 + 24, 40)) = '0xb35033d71cf5e13cab5eb8618260f94363dff9cf' then 'LFJ'

when concat('0x', substring(INPUT_DATA, 11 + 24, 40)) = '0xc04f291347d21dc663f7646056db22bff8ce8430' then (

CASE

WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '88de50b233052e4fb783d4f6db78cc34fea3e9fc' THEN 'ODOS'

WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '6131b5fae19ea4f9d964eac0408e4408b66337b5' THEN 'KYBER'

WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '38575264810371c15f0e5744fa2ab29cdef7245d' THEN 'OKX'

WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '1dac23e41fc8ce857e86fd8c1ae5b6121c67d96d' THEN 'OKX'

WHEN SUBSTRING(INPUT_DATA, 11 + 24 + 8 * 64 + 2 * 40, 40) = '3611b82c7b13e72b26eb0e9be0613bee7a45ac7c' THEN 'FLY.TRADE'