Query editor
WITH mint_event AS (SELECTl.contract_address,'Mint' AS event_name,l.block_time,l.block_number,l.tx_hash,-- Decode owner from topic1 (addresses use 20 bytes starting at offset 13)bytearray_substring(l.topic1, 13, 20) AS owner,-- Decode tick values (full 32-byte with sign extension)bytearray_to_int256(l.topic2) AS tickLower,bytearray_to_int256(l.topic3) AS tickUpper,-- Decode sender from data (last 20 bytes of the first 32 bytes)bytearray_substring(l.data, 13, 20) AS sender,-- Decode liquidity (scaled by 1e18)bytearray_to_uint256(bytearray_substring(l.data, 33, 32)) / 1e18 AS liquidity,-- Decode token amounts (assumed 18 decimals)bytearray_to_uint256(bytearray_substring(l.data, 65, 32)) / 1e18 AS amount0,bytearray_to_uint256(bytearray_substring(l.data, 97, 32)) / 1e18 AS amount1FROM base.logs lWHERE l.contract_address = 0x2b70cea59a418d77265482564610EF4D681D5ad6AND l.topic0 = 0x7a53080ba414158be7ec69b987b5fb7d07dee101fe85488f0853ae16239d0bde),-- *******************-- Pricing and market logic (from swap events and oracle feeds)-- *******************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,bytearray_to_int256(bytearray_substring(l.data, 1, 32)) / 1e18 AS amount_token0,bytearray_to_int256(bytearray_substring(l.data, 33, 32)) / 1e18 AS amount_token1,-- Calculate token prices from the sqrtPriceX96 (Q96 fixed point) value:POWER(bytearray_to_int256(bytearray_substring(l.data, 65, 32)) / POWER(2, 96), 2) AS price_token0_token,1 / POWER(bytearray_to_int256(bytearray_substring(l.data, 65, 32)) / POWER(2, 96), 2) AS price_token1_token,bytearray_to_int256(bytearray_substring(l.data, 97, 32)) / 1e18 AS liquidity,bytearray_to_int256(bytearray_substring(l.data, 137, 32)) AS tick,POWER(1.0001, bytearray_to_int256(bytearray_substring(l.data, 137, 32))) AS price_from_tickFROM base.logs lWHERE l.topic0 = 0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67AND l.contract_address = 0x2B70CeA59a418d77265482564610EF4D681D5ad6),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,