Nige7777Copy of Top 10 Uniswap V3 Liquidity Providers
Updated 2021-05-24
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
›
⌄
with cte_1 as (
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_Total_USD_Value_Of_LP
,last_value(Liquidity_adjusted ) OVER (partition by liquidity_provider , pool_name, nf_token_id order by block_id) AS LP_size
,nf_token_id
,pool_name
,pool_address
,liquidity_provider
from uniswapv3.lp_actions
where liquidity_provider ='0x89cd98a69ac5dcff78c5f436a5a34e90bb257b84'
)
,
cte_lps as (
select
SUM(Current_Total_USD_Value_Of_LP) Current_Total_USD_Value_Of_LP
,pool_name
,c1.liquidity_provider
from cte_1 c1
group by c1.liquidity_provider, pool_name
order by Current_Total_USD_Value_Of_LP
desc
limit 10
)
, cte_totals as (
select
Current_Total_USD_Value_Of_LP
,a.liquidity_provider
-- ,lp.total_usd_per_LP
, a.pool_name
,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 and a.pool_name = lp.pool_name
group by
a.liquidity_provider
,Current_Total_USD_Value_Of_LP
, a.pool_name
-- ,Pool_Fees_USD
order by lp.Current_Total_USD_Value_Of_LP desc
)
select
Current_Total_USD_Value_Of_LP
,t.liquidity_provider
, t.pool_name
, t.Total_Pool_positions
--,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.pool_name
, t.Total_Pool_positions
, Current_Total_USD_Value_Of_LP
order by Current_Total_USD_Value_Of_LP desc
Run a query to Download Data