Nige7777Copy of Top v3 users
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
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)
,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
,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((amount0_usd + amount1_USD) * difference ) total_usd_per_LP
,liquidity_provider
from cte_1 c1
group by liquidity_provider
order by total_usd_per_LP desc
limit 10
)
select
SUM(ifnull(amount1_USD,0) + ifnull(amount0_usd,0) ) as Pool_USD
,a.liquidity_provider
,pool_name
,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
,pool_name
order by Pool_USD desc
limit 20
Run a query to Download Data