Nige7777Top v3 users
Updated 2021-11-11
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
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
›
⌄
with cte_1 as (
select
sum (case when action = 'INCREASE_LIQUIDITY' then liquidity_adjusted when action = 'DECREASE_LIQUIDITY' then -liquidity_adjusted end) over (partition by liquidity_provider,pool_name,nf_token_id order by block_id) liquid
--,datediff(minute,'1990-1-1',block_timestamp)
,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
,abs(datediff('hour', block_timestamp ,coalesce (lead(block_timestamp) over (partition by liquidity_provider , pool_name, nf_token_id order by block_id),getdate()))) as difference
-- ,lag(block_id) over (partition by liquidity_provider , pool_name order by block_id) dddd
-- ,lag(datediff(minute,'1990-1-1',block_timestamp)) over (partition by liquidity_provider , pool_name order by datediff(minute,'1990-1-1',block_timestamp)) numLag
-- ,lag(block_timestamp) over (partition by liquidity_provider,pool_name order by block_id) bid
,nf_token_id
,liquidity_adjusted
,liquidity_provider
,ifnull(amount1_USD,0) amount1_USD
,ifnull(amount0_usd,0) amount0_usd
,action
,pool_name
,pool_address
,block_id
,date_trunc('day',block_timestamp) d
,block_timestamp
-- ,min(date_trunc('day',block_timestamp) ) over (partition by date_trunc('day',block_timestamp) order by block_timestamp) as Day
from uniswapv3.lp_actions
order by block_id
)
,
cte_lps as (
select
--SUM((c1.amount0_usd + c1.amount1_USD) * difference ) total_usd_per_LP
SUM( ifnull(amount1_USD,0) + ifnull(amount0_usd,0) ) AS Total_Liquidity_All_Pools
, SUM(Current_USD_Value_Of_Pool) Current_USD_Value_Of_Pool
,c1.liquidity_provider
from cte_1 c1
-- left join uniswapv3.Position_collected_fees f on f.liquidity_provider = c1.liquidity_provider
group by c1.liquidity_provider
order by Total_Liquidity_All_Pools desc
limit 10
)
, cte_totals as (
select
lp.Total_Liquidity_All_Pools
,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
--,lp.total_usd_per_LP
,lp.Total_Liquidity_All_Pools
,Current_USD_Value_Of_Pool
-- ,Pool_Fees_USD
order by lp.Total_Liquidity_All_Pools 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.total_usd_per_LP
t.liquidity_provider
, t.Total_pools
, t.Total_Pool_positions
,t.Total_Liquidity_All_Pools
,Current_USD_Value_Of_Pool
order by Total_Liquidity_All_Pools desc
Run a query to Download Data