Nige7777current_postitions
Updated 2021-05-30
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
›
⌄
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
)
SELECT
p.pool_address,
pool_name,
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 THEN 'in price'
ELSE 'above 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
Run a query to Download Data