Nige7777V3 Top LP position changes
Updated 2022-01-13
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
157
158
159
160
161
162
163
164
165
166
167
›
⌄
WITH max_pos_blocks AS (
SELECT
max(block_id) AS block_id,
pool_address,
liquidity_provider,
nf_token_id
FROM
uniswapv3.positions
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_lps as (
select
SUM(current_amount_0_usd + current_amount_1_usd) Current_Total_USD_Value_Of_LP
,c1.liquidity_provider
from cte_results c1
group by c1.liquidity_provider
order by Current_Total_USD_Value_Of_LP
desc
limit 10
)
select
r.pool_name,
count(distinct r.liquidity_provider) LPs_in_pool
,count(*) LP_Position_Changes
,sum(t.fee_usd) Gas_Fees_Paid
,avg(gas_price) Avg_Gas_Price
from cte_results r
left join uniswapv3.lp_actions a on a.liquidity_provider = r.liquidity_provider and r.pool_name = a.pool_name
left join ethereum.transactions t on t.tx_id = a.tx_id
where r.liquidity_provider in (select liquidity_provider from cte_lps)
group by r.pool_name
order by LP_Position_Changes desc
-- select
-- avg(p.price_lower_1_0_USD) price_lower_1_0_USD,
-- (avg(price_lower_1_0_USD) + avg(price_upper_1_0_USD)) / 2 as middle_price,
-- avg(p.price_upper_1_0_USD) price_upper_1_0_USD,
-- date_trunc('hour', p.block_timestamp) as hour_,
-- date_trunc('day', p.block_timestamp) as day_,
-- avg(e.price) as ETH_Price
-- --avg(lv_liquidity) liquidity
-- from uniswapv3.lp_actions p
-- left join uniswapv3.position_collected_fees f on f.tx_id = p.tx_id
-- left join ethereum.token_prices_hourly e on e.hour = date_trunc('hour', p.block_timestamp) and e.symbol = 'ETH'
-- inner JOIN cte_lps lps on lps.liquidity_provider = p.liquidity_provider
-- where
-- p.pool_name like 'WETH-USDT%' --'USDC_WETH%'
-- and p.block_timestamp >= '2021-05-05T15:00:00Z'
-- group by
-- date_trunc('hour', p.block_timestamp) ,
-- date_trunc('day', p.block_timestamp),
-- e.price
-- order by
-- day_ desc
Run a query to Download Data