WITH pools as ( SELECT pool_address as address, max(block_timestamp) max_ts FROM uniswapv3.pools where pool_address ='0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' and block_timestamp > CURRENT_DATE - INTERVAL '1 hour' GROUP BY pool_address ), all_positions as ( SELECT * FROM ( SELECT DISTINCT liquidity_provider, last_value(liquidity_adjusted) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as liquidity_adj, nf_token_id, last_value(tick_upper) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as tick_upper, last_value(tick_lower) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as tick_lower, last_value(price_lower_0_1_usd) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as price_lower_0_1_usd, last_value(price_upper_0_1_usd) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as price_upper_0_1_usd, last_value(price_lower_1_0_usd) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as price_lower_1_0_usd, last_value(price_upper_1_0_usd) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as price_upper_1_0_usd, pool_address, pool_name, case when price_upper_1_0_usd < 500 then '1. <500$' when price_upper_1_0_usd < 1000 then '2. 500$-1000$' when price_upper_1_0_usd < 1500 then '3. 1000$-1500$' when price_upper_1_0_usd < 2000 then '4. 1500$-2000$' when price_upper_1_0_usd < 2500 then '5. 2000$-2500$' when price_upper_1_0_usd < 3000 then '6. 2500$-3000$' when price_upper_1_0_usd < 3500 then '7. 3000$-3500$' when price_upper_1_0_usd < 4000 then '8. 3500$-4000$' when price_upper_1_0_usd < 4500 then '9. 4000$-4500$' when price_upper_1_0_usd < 7500 then '10. 4500$-7500$' when price_upper_1_0_usd < 10000 then '11. 7500$-10000$' when price_upper_1_0_usd < 15000 then '12. 10000$-15000$' when price_upper_1_0_usd > 15000 then '13. 15000$' end as category FROM uniswapv3.positions pos WHERE pos.pool_address = '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' AND pos.is_active ) allpos WHERE allpos.liquidity_adj > 0 ) SELECT * FROM all_positions;

Uniswap USDC / ETH Pool Positions Dashboard