Query editor
-- Get Swap interactions with CSMM (constant product market maker) hookwith selected_pools as (select id as pool_id -- 28, currency0, currency1, fee / 1e4 as pool_fee_pct, hooksfrom uniswap_v4_sepolia.PoolManager_evt_Initializewhere hooks = 0x1EC90889C6633A0d01932fbf83be93c22d194888 -- CSMM), fixed_bytes as (select s.evt_block_time, s.evt_tx_hash-- , s.id as pool_id-- , s.sender-- , s.amount0-- , s.amount1-- , s.sqrtPriceX96-- , s.liquidity, s.fee / 1e4 as swap_fee_pct, b.output_1 as returnDelta, CAST(json_extract(params, '$.zeroForOne') AS boolean) AS zeroForOne, json_extract(params, '$.amountSpecified') as amountSpecified, CAST(b.output_1 AS varbinary) AS int256_varbinaryfrom uniswap_v4_sepolia.PoolManager_evt_Swap sleft join uniswap_v4_hooks_sepolia.CSMM_call_beforeSwap bon b.call_successand b.call_tx_hash = s.evt_tx_hashand b.call_block_number = s.evt_block_numberwhere id in (select pool_id from selected_pools)order by 1 desc), wrangled as (select *-- , varbinary_to_int256(varbinary_substring(int256_varbinary, 1, 16)) AS high_bits -- Extract the top 128 bits, then convert to int-- , varbinary_to_int256(varbinary_substring(int256_varbinary, 17, 16)) AS low_bits -- Extract the bottom 128 bits, then convert to int-- 1) Check sign bit in the "top 128 bits" first byte, CASEWHEN bitwise_and(varbinary_to_bigint(varbinary_substring(int256_varbinary, 1, 1)),from_base('80', 16) -- 0x80 as decimal 128) = from_base('80', 16)THEN varbinary_to_int256(varbinary_concat(from_hex('0xFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'), -- 16 bytes of 0xFFvarbinary_substring(int256_varbinary, 1, 16) -- the top 16 bytes))ELSE varbinary_to_int256(varbinary_concat(from_hex('0x00000000000000000000000000000000'), -- 16 bytes of 0x00varbinary_substring(int256_varbinary, 1, 16))