RamaharwBTC On- and Off- Ramps
Updated 2022-06-04Copy Reference Fork
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
›
⌄
with addBTC as (select
DATE(block_timestamp) as dayz,
liquidity_provider_address as addusers,
sum(amount) / 1e8 as addamt
from osmosis.core.fact_liquidity_provider_actions
where currency = 'ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F'
AND tx_status = 'SUCCEEDED' AND action = 'pool_joined'
group by 1, 2),
removeBTC as (select
DATE(block_timestamp) as dt,
liquidity_provider_address as removeusers,
sum(amount) / 1e8 as removeamt
from osmosis.core.fact_liquidity_provider_actions
where currency = 'ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F'
AND tx_status = 'SUCCEEDED' AND action = 'pool_exited'
group by 1, 2),
wBTCpos as (select
dayz,
addusers,
(addamt - coalesce(removeamt, 0)) as Netamt,
sum(Netamt) over (partition by addusers order by dayz asc rows between unbounded preceding and current row) as balance
from addBTC a
left join removeBTC ON dayz=dt AND addusers = removeusers
order by dayz ASC )
select *
from wBTCpos
where balance < 40
-- ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F wBTC
-- ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5 wETH
Run a query to Download Data