lb_swap_tb as (

select

block_timestamp,

tx_hash,

--15 * 10000 * 0.0001 * 0.0001 as fees, -- 0.15%

ethereum.public.udf_hex_to_int (substr(data, 1 + 2 + 64 * 0, 64)) as swapForY,

to_double(ethereum.public.udf_hex_to_int (substr(data, 1 + 2 + 64 * 1, 64))) as amountIn,

to_double(ethereum.public.udf_hex_to_int (substr(data, 1 + 2 + 64 * 2, 64))) as amountOut,

ethereum.public.udf_hex_to_int (substr(data, 1 + 2 + 64 * 3, 64)) as volatilityAccumulated,

ethereum.public.udf_hex_to_int (substr(data, 1 + 2 + 64 * 4, 64)) as fees,

case when swapForY=1 then amountOut*pow(10,-6) --swapForUSDC out=usdc

when swapForY=0 then amountIn*pow(10,-6) end as amount0,

case when swapForY=1 then amountIn*pow(10,-18) --swapForUSDC out=usdc

when swapForY=0 then amountOut*pow(10,-18) end as amount1,

case when swapForY=1 then (amountIn-fees)*pow(10,-18)

when swapForY=0 then amountOut*pow(10,-18) end as eth_Swapped,

case when swapForY=1 then (amountOut)*pow(10,-6) --swapForUSDC out=usdc

when swapForY=0 then (amountIn-fees)*pow(10,-6) end as amount_usd,

div0(amount_usd,eth_swapped) as swapPrice,

case when swapForY=0 then fees*pow(10,-6)

when swapForY=1 then fees*pow(10,-18)*swapprice end as fees_collected_usd,

case

when swapForY=1 then 1 -- swapForUSDC, eth into lp, usdc out

when swapForY=0 then -1

end as direction

from

arbitrum.core.fact_event_logs

where

contract_address = lower('0x7ec3717f70894f6d9ba0be00774610394ce006ee')

and topics[0] = '0xc528cda9e500228b16ce84fadae290d9a49aecb17483110004c5af0a07f6fd73'