with cte_top_10 as (

select count(*), liquidity_provider

from uniswapv3.lp_actions

where 1=1

and pool_address in

(

'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',

'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',

'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'

)

and block_timestamp >= CURRENT_DATE -30

GROUP BY liquidity_provider

ORDER BY count(*) DESC

limit 10

),

cte_details AS

(

select distinct

ps.price_0_1,

a.block_id,

a.pool_name,

a.liquidity_provider,

a.nf_token_id,

p.is_active,

CASE WHEN p.liquidity_adjusted = 0 and a.action = 'DECREASE_LIQUIDITY' THEN 'CLOSE' ELSE 'OPEN' END AS STATE,

lag (date_trunc(hour, a.block_timestamp) ) OVER (Partition by a.NF_Token_ID ORDER BY a.block_id) AS last_action_,

p.liquidity_adjusted as pAdjusted,

a.action,

a.liquidity_adjusted,

date_trunc(hour, a.block_timestamp) as LP_hour,

amount0_adjusted,

amount1_adjusted,

amount0_usd,