with topR as (

SELECT distinct

rank () OVER (PARTITION BY pool_address, pool_name ORDER BY liquidity_adj desc) as TopRanking,

* FROM (

SELECT DISTINCT

liquidity_provider,

nf_token_id,

pool_address,

pool_name,

last_value(liquidity_adjusted) OVER(PARTITION BY liquidity_provider, nf_token_id, pool_address, pool_name ORDER BY block_timestamp) as liquidity_adj,

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

FROM uniswapv3.positions pos

WHERE pos.pool_address in ( '0xcbcdf9626bc03e24f779434178a73a0b4bad62ed',

'0x1d42064fc4beb5f8aaf85f4617ae8b3b5b8bd801',

'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',

'0x4e68ccd3e89f51c3074ca5072bbac773960dfa36',

'0x7858e59e0c01ea06df3af3d20ac7b0003275d4bf')

AND pos.block_timestamp > '2021-05-05 12:00:00'

) allpos

WHERE allpos.liquidity_adj > 0

order by liquidity_adj desc

select distinct p.liquidity_provider,trunc(p.liquidity_adjusted) AS liquidity_adjusted , p.pool_name, p.price_lower_1_0_usd, p.price_upper_1_0_usd, date_trunc('hour',p.block_timestamp) as hour from topR r

inner join uniswapv3.lp_actions p on r.liquidity_provider = p.liquidity_provider and r.pool_name = p.pool_name

where r.TopRanking <5 and r.pool_name = 'WETH-USDT 3000 60' and p.liquidity_adjusted > 10000

order by liquidity_adjusted desc