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,

last_value(date_trunc(hour, block_timestamp) ) over ( partition by date_trunc(hour, block_timestamp) order by block_id ) as P_hour,

last_value(block_id) over (partition by date_trunc(hour, block_timestamp) order by block_id ) as pBlock_id

FROM

uniswapv3.pool_stats p

WHERE 1=1

AND p.pool_address in

(

'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',

'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',

'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'

)