select distinct

last_value(ifnull(amount1_USD,0) + ifnull(amount0_usd,0) ) OVER (partition by liquidity_provider , pool_name, nf_token_id order by block_id) AS Current_USD_Value_Of_Pool

,nf_token_id

,pool_name

,pool_address

,liquidity_provider

from uniswapv3.lp_actions where liquidity_provider =

)

,

cte_lps as (

select

SUM(Current_USD_Value_Of_Pool) Current_USD_Value_Of_Pool

,c1.liquidity_provider

from cte_1 c1

group by c1.liquidity_provider

order by Current_USD_Value_Of_Pool desc

limit 10

, cte_totals as (

select

Current_USD_Value_Of_Pool

,a.liquidity_provider

-- ,lp.total_usd_per_LP

,count(distinct a.pool_name) Total_pools

,count(distinct nf_token_id) Total_Pool_positions

from

uniswapv3.lp_actions a

inner join cte_lps lp on lp.liquidity_provider = a.liquidity_provider

group by

a.liquidity_provider

,Current_USD_Value_Of_Pool

order by lp.Current_USD_Value_Of_Pool desc

select t.*

,SUM(ifnull(cf.amount1_USD,0) + ifnull(cf.amount0_usd,0) ) as Pool_Fees_Earned_USD

from cte_totals t

left join uniswapv3.Position_collected_fees cf on cf.liquidity_provider = t.liquidity_provider

group by

t.liquidity_provider

, t.Total_pools

, t.Total_Pool_positions

,Current_USD_Value_Of_Pool

order by Current_USD_Value_Of_Pool desc