WITH max_pos_blocks AS ( SELECT max(block_id) AS block_id, pool_address, liquidity_provider, nf_token_id FROM ethereum.uniswapv3.ez_positions WHERE nf_token_id = 329003 GROUP BY pool_address, liquidity_provider, nf_token_id ), max_stats_blocks AS ( SELECT max(block_id) AS block_id, pool_address FROM ethereum.uniswapv3.ez_pool_stats GROUP BY pool_address ), current_prices AS ( SELECT price_0_1 AS current_price_0_1, token0_balance_usd / token0_balance_adjusted AS price0_usd, price_1_0 AS current_price_1_0, token1_balance_usd / token1_balance_adjusted AS price1_usd, p.pool_address FROM ethereum.uniswapv3.ez_pool_stats p INNER JOIN max_stats_blocks mb ON mb.block_id = p.block_id AND mb.pool_address = p.pool_address WHERE nf_token_id = 329003 --WHERE token0_balance_adjusted > 0 AND token1_balance_adjusted > 0 ) SELECT p.pool_address, pool_name, p.liquidity_provider, p.nf_token_id, liquidity_adjusted, current_price_1_0, price_lower_1_0, price_upper_1_0, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 'above price' WHEN current_price_1_0 < price_upper_1_0 THEN 'in price' ELSE 'above price' END AS position_state, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ELSE 0 END AS current_amount_0, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN ( liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0) WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0) ELSE 0 END AS current_amount_0_usd, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 0 WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ELSE liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) END AS current_amount_1, CASE WHEN current_price_1_0 <= price_lower_1_0 THEN 0 WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0) ELSE ( liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0) END AS current_amount_1_usd FROM ethereum.uniswapv3.ez_positions p INNER JOIN max_pos_blocks mb ON mb.block_id = p.block_id AND mb.pool_address = p.pool_address AND mb.liquidity_provider = p.liquidity_provider AND mb.nf_token_id = p.nf_token_id JOIN current_prices cp ON p.pool_address = cp.pool_address WHERE liquidity_adjusted > 0

Uniswap V3 Current value of Positions