Query editor
WITHget_recent_sqrtPriceX96 AS (SELECT *FROM (SELECTml.*,i.currency0 as token0,i.currency1 as token1,COALESCE(s.evt_block_time, i.evt_block_time) as most_recent_time,COALESCE(s.sqrtPriceX96, i.sqrtPriceX96) AS sqrtPriceX96,ROW_NUMBER() OVER (PARTITION BY ml.id, ml.evt_block_timeORDER BYCASE WHEN s.sqrtPriceX96 IS NOT NULL THEN s.evt_block_time ELSE i.evt_block_time END DESC) AS rnFROMuniswap_v4_sepolia.PoolManager_evt_ModifyLiquidity mlLEFT JOINuniswap_v4_sepolia.PoolManager_evt_Swap sON ml.evt_block_time > s.evt_block_time AND ml.id = s.idLEFT JOINuniswap_v4_sepolia.PoolManager_evt_Initialize iON ml.evt_block_time >= i.evt_block_time AND i.id = ml.id)tblWHERE rn = 1),prep_for_calculations AS (SELECT evt_block_time,evt_block_number,id,evt_tx_hash,evt_index,salt,token0,token1,LOG(sqrtPriceX96/POWER(2, 96), 10)/LOG(1.0001, 10) as tickCurrent,tickLower,tickUpper,SQRT(POWER(1.0001, tickLower)) as sqrtRatioL,SQRT(POWER(1.0001, tickUpper)) sqrtRatioU,sqrtPriceX96/ POWER(2, 96) sqrtPrice,sqrtPriceX96,liquidityDeltaFROM get_recent_sqrtPriceX96),base_amounts AS (SELECTevt_block_time,evt_block_number,id,evt_tx_hash,evt_index,salt,token0,token1,CASE WHEN sqrtPrice <= sqrtRatioL THEN liquidityDelta * ((sqrtRatioU - sqrtRatioL)/(sqrtRatioL*sqrtRatioU))WHEN sqrtPrice >= sqrtRatioU THEN 0ELSE liquidityDelta * ((sqrtRatioU - sqrtPrice)/(sqrtPrice*sqrtRatioU))END as amount0,CASE WHEN sqrtPrice <= sqrtRatioL THEN 0