Query editor
WITH market_price AS (SELECTl.contract_address,'Swap' AS event_name,DATE(CAST(l.block_time AS DATE)) AS date,l.block_time,l.block_number,l.tx_hash,bytearray_substring(l.topic1, 13, 20) AS sender,bytearray_substring(l.topic2, 13, 20) AS recipient,-- Extract swap amounts (assuming 18 decimals)bytearray_to_int256(bytearray_substring(data, 1, 32)) / 1e18 AS amount_token0,bytearray_to_int256(bytearray_substring(data, 33, 32)) / 1e18 AS amount_token1,-- Compute price using sqrtPriceX96POWER(bytearray_to_int256(bytearray_substring(data, 65, 32)) / POWER(2, 96), 2) AS price_token0_token,1 / POWER(bytearray_to_int256(bytearray_substring(data, 65, 32)) / POWER(2, 96), 2) AS price_token1_token,-- Extract liquidity & tickbytearray_to_int256(bytearray_substring(data, 97, 32)) / 1e18 AS liquidity,bytearray_to_int256(bytearray_substring(data, 137, 32)) AS tick,POWER(1.0001, bytearray_to_int256(bytearray_substring(data, 137, 32))) AS price_from_tickFROM base.logs lWHERE l.topic0 = 0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67AND l.contract_address = 0x2B70CeA59a418d77265482564610EF4D681D5ad6),-- Oracle price extraction (unchanged)oracle_price AS (SELECTl.contract_address,'AnswerUpdated' AS event_name,l.block_time,l.block_number,l.tx_hash,l.topic0 AS event_signature,bytearray_to_uint256(bytearray_substring(l.topic1, 1, 32)) AS current,bytearray_to_uint256(bytearray_substring(l.topic2, 1, 32)) AS roundId,bytearray_to_uint256(bytearray_substring(data, 1, 32)) AS updatedAtFROM base.logs lWHERE l.topic0 = 0x0559884fd3a460db3073b7fc896cc77986f16e378210ded43186175bf646fc5fAND l.contract_address = 0x6f9829A2278A5b017b6e997E2BafCDbCb8d6Bc04),oracle_price_usd AS (SELECTblock_number,AVG(current / 1e18) AS priceFROM oracle_priceGROUP BY block_number),latest_previous_tcap AS (SELECTprice AS TCAP_price,block_number,LAG(block_number) OVER (ORDER BY block_number DESC) AS previous_block_number,block_number AS latest_block_number