Nige7777Copy of Positions out of range
Updated 2021-06-03
999
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
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
›
⌄
WITH max_pos_blocks AS (
SELECT
max(block_id) AS block_id,
pool_address,
liquidity_provider,
nf_token_id
FROM
uniswapv3.positions
WHERE
GROUP BY
pool_address,
liquidity_provider,
nf_token_id
),
max_stats_blocks AS (
SELECT
max(block_id) AS block_id,
pool_address
FROM
uniswapv3.pool_stats
GROUP BY
pool_address
),
current_prices AS (
SELECT
price_0_1 AS current_price_0_1,
token0_balance_usd / token0_balance_adjusted AS price0_usd,
price_1_0 AS current_price_1_0,
token1_balance_usd / token1_balance_adjusted AS price1_usd,
p.pool_address
FROM
uniswapv3.pool_stats p
INNER JOIN max_stats_blocks mb ON
mb.block_id = p.block_id AND
mb.pool_address = p.pool_address
--WHERE token0_balance_adjusted > 0 AND token1_balance_adjusted > 0
)
,cte_results as(
SELECT
p.pool_address,
pool_name,
left(pool_name, REGEXP_INSTR(pool_name,'\\s\\d')) Pool_,
p.liquidity_provider,
p.nf_token_id,
liquidity_adjusted,
current_price_1_0,
price_lower_1_0,
price_upper_1_0,
CASE
WHEN current_price_1_0 <= price_lower_1_0 THEN 'above price'
WHEN current_price_1_0 < price_upper_1_0 and price_lower_1_0 <= current_price_1_0 THEN 'in price'
ELSE 'below price'
END AS position_state,
CASE
WHEN current_price_1_0 <= price_lower_1_0 THEN liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0)
WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0)
ELSE 0
END AS current_amount_0,
CASE
WHEN current_price_1_0 <= price_lower_1_0 THEN ( liquidity_adjusted / sqrt(price_lower_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0)
WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted / sqrt(current_price_1_0) - liquidity_adjusted / sqrt(price_upper_1_0) ) * COALESCE(price0_usd, 0)
ELSE 0
END AS current_amount_0_usd,
CASE
WHEN current_price_1_0 <= price_lower_1_0 THEN 0
WHEN current_price_1_0 < price_upper_1_0 THEN liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0)
ELSE liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0)
END AS current_amount_1,
CASE
WHEN current_price_1_0 <= price_lower_1_0 THEN 0
WHEN current_price_1_0 < price_upper_1_0 THEN ( liquidity_adjusted * sqrt(current_price_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0)
ELSE ( liquidity_adjusted * sqrt(price_upper_1_0) - liquidity_adjusted * sqrt(price_lower_1_0) ) * COALESCE(price1_usd, 0)
END AS current_amount_1_usd
FROM uniswapv3.positions p
INNER JOIN max_pos_blocks mb ON
mb.block_id = p.block_id AND
mb.pool_address = p.pool_address AND
mb.liquidity_provider = p.liquidity_provider AND
mb.nf_token_id = p.nf_token_id
JOIN current_prices cp ON p.pool_address = cp.pool_address
WHERE liquidity_adjusted > 0
)
, cte_totals as (
select distinct
SUM(current_amount_1_usd+current_amount_0_usd) OVER (partition by pool_ ORDER BY pool_) Total_USD_Amount,
sum(current_amount_1_usd+current_amount_0_usd) OVER (partition by pool_, position_state ORDER BY pool_) AS Total_USD_IN_STATE,
pool_address as Tpool_address,
--pool_name as TPool_Name,
Pool_ AS Pool_Name,
position_state
FROM cte_results
order by Total_USD_Amount desc
)
, cte_last as(
select
dense_rank() over ( ORDER BY Total_USD_Amount desc , pool_name ) Ranking,
*
from cte_totals t
order by Total_USD_Amount desc
)
select
Pool_ AS Pool,
position_state,
'https://app.uniswap.org/#/pool/' || nf_token_id AS Link_to_Liquidity,
current_amount_1_usd+current_amount_0_usd as Liquidity_in_USD,
price_lower_1_0 AS Min_Price,
price_upper_1_0 as Max_Price,
current_amount_1_usd as Token_1_USD ,
current_amount_0_usd AS Token_2_USD,
liquidity_provider AS LP_Address,
CASE WHEN
from
cte_results r
where r.position_state in ('above price' ,'below price')
and r.pool_ in (select Pool_Name from cte_last where Ranking <=20)
and Liquidity_in_USD > 50000
order by Liquidity_in_USD desc
Run a query to Download Data