connorhDiscord checkers
Updated 2021-08-01
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
›
⌄
WITH ticks AS (
SELECT pool_address,date_trunc('hour',block_timestamp) AS hour, MIN(tick) AS tick_lower_pool, MAX(tick) AS tick_upper_pool
FROM uniswapv3.pool_stats
WHERE pool_address = '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8'
GROUP BY 1,2
)
select
p.tx_id,
p.block_timestamp,
p.nf_token_id,
p.liquidity_provider,
p.pool_name,
p.pool_address,
p.liquidity_adjusted,
p.tokens_owed0_adjusted,
p.tokens_owed1_adjusted,
l.action,
l.amount0_adjusted,
l.amount1_adjusted,
CASE WHEN p.tick_upper < s.tick_lower_pool OR p.tick_lower > s.tick_upper_pool THEN 'Out of range' ELSE 'In range' END AS in_range,
p.is_active,
s.tick_lower_pool,
s.tick_upper_pool,
p.tick_lower,
p.tick_upper
from uniswapv3.positions p
left join uniswapv3.lp_actions l on (p.tx_id = l.tx_id)
left join ticks s on (p.pool_address = s.pool_address AND date_trunc('hour',p.block_timestamp) = s.hour)
where p.pool_name in ('USDC-WETH 3000 60')
and p.liquidity_provider in ('0xd71f271ffa7a6580e5710feb7279d795a8272a66')
and p.is_active = TRUE
order by 2
Run a query to Download Data