Query editor
WITH increase_liquidity AS (SELECTl.contract_address,'IncreaseLiquidity' AS event_name,l.block_time,l.block_number,l.tx_hash,-- Decode tokenId from topic1 (using last 20 bytes, adjust if needed)bytearray_to_int256(bytearray_substring(l.topic1, 13, 20)) AS tokenId,-- Decode liquidity added from the data payloadbytearray_to_uint256(bytearray_substring(l.data, 1, 32)) / 1e18 AS added_liquidity,-- Decode token0 and token1 amounts added (assuming 18 decimals for both)bytearray_to_uint256(bytearray_substring(l.data, 33, 32)) / 1e18 AS added_amount0,bytearray_to_uint256(bytearray_substring(l.data, 65, 32)) / 1e18 AS added_amount1FROM base.logs lWHERE l.contract_address = 0x03a520b32c04bf3beef7beb72e919cf822ed34f1AND l.topic0 = 0x3067048beee31b25b2f1681f88dac838c8bba36af25bfb2b7cf7473a5847e35fAND l.tx_hash IN (SELECT tx_hashFROM base.logsWHERE contract_address = 0x2B70CeA59a418d77265482564610EF4D681D5ad6)),-- *******************-- Market price logic (from swap events)-- *******************market_price AS (SELECTl.contract_address,'Swap' AS event_name,DATE(CAST(l.block_time AS DATE)) AS date, -- Convert block_time to datel.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 amount of token0 swapped (assumed 18 decimals)bytearray_to_int256(bytearray_substring(l.data, 1, 32)) / 1e18 AS amount_token0,-- Extract amount of token1 swapped (assumed 18 decimals)bytearray_to_int256(bytearray_substring(l.data, 33, 32)) / 1e18 AS amount_token1,-- Calculate price of token0 in terms of token1 using sqrtPriceX96:-- sqrtPriceX96 is a Q96 fixed-point number; dividing by 2^96 and squaring gives the price.POWER(bytearray_to_int256(bytearray_substring(l.data, 65, 32)) / POWER(2, 96), 2) AS price_token0_token,-- Calculate the inverse price: token1 in terms of token0.1 / POWER(bytearray_to_int256(bytearray_substring(l.data, 65, 32)) / POWER(2, 96), 2) AS price_token1_token,-- Extract liquidity (for context)bytearray_to_int256(bytearray_substring(l.data, 97, 32)) / 1e18 AS liquidity,-- Extract tick from the swap event (as a full 32-byte int)bytearray_to_int256(bytearray_substring(l.data, 137, 32)) AS tick,-- Calculate price from tick (price = 1.0001^tick)POWER(1.0001, bytearray_to_int256(bytearray_substring(l.data, 137, 32))) AS price_from_tickFROM base.logs lWHERE l.topic0 = 0xc42079f94a6350d7e6235f29174924f928cc2ac818eb64fed8004e115fbcca67