-- lag (CASE when postition_state = 'in price' THEN lp_hour ELSE NULL END) OVER (Partition by d.NF_Token_ID ORDER BY d.block_id) AS Hour_of_last_action,

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_prices AS (

SELECT distinct

last_value(price_0_1) over ( partition by date_trunc(hour, block_timestamp) order by block_id ) AS current_price_0_1,

last_value(token0_balance_usd / nullifzero(token0_balance_adjusted) ) over ( partition by date_trunc(hour, block_timestamp) order by block_id ) AS price0_usd,

last_value(price_1_0) over ( partition by date_trunc(hour, block_timestamp) order by block_id ) AS current_price_1_0,

last_value(token1_balance_usd /nullifzero( token1_balance_adjusted) ) over ( partition by date_trunc(hour, block_timestamp) order by block_id ) AS price1_usd,

-- p.pool_address,

date_trunc(hour, block_timestamp) as P_hour,

block_id as pBlock_id

FROM