keshanwETH & wBTC On- and Off- Ramps
Updated 2022-06-07
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
35
36
›
⌄
-- Pool ID 712 = wBTC = ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F
-- Pool ID 704 = wETH = ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5
with prices as (select date_trunc('hour', block_timestamp) as hour,
(case when to_currency='Osmosis' then from_currency when from_currency='Osmosis' then to_currency else null end) as token,
avg(price) as price
from (select block_timestamp,
(case when from_currency='uosmo' then (from_amount/pow(10, from_decimal)) / (to_amount/pow(10, to_decimal)) when to_currency='uosmo' then (to_amount/pow(10, to_decimal)) / (from_amount/pow(10, from_decimal)) else null end) as price,
(case when t.label is null then to_currency else t.label end) as to_currency,
(case when f.label is null then from_currency else f.label end) as from_currency
from osmosis.core.fact_swaps
left join osmosis.core.dim_labels t on t.address=to_currency
left join osmosis.core.dim_labels f on f.address=from_currency)
where price is not null and token != 'Osmosis' and (token='Wrapped Ether' or token='Wrapped Bitcoin')
group by hour, token), -- For getting prices, Reusing one of my previous queries which I wrote for a different bounty.
btc as (select date_trunc('hour', block_timestamp) as date,
sum(case when action='pool_joined' then case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then price * amount/pow(10, DECIMAL) else (amount/pow(10, DECIMAL)) end end) as added_liquidity_to_wbtc,
sum(case when action='pool_exited' then case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then price * (-1 * amount/pow(10, DECIMAL)) else (-1 * amount/pow(10, DECIMAL)) end end) as withdraw_liquidity_from_wbtc,
case when currency='ibc/D1542AA8762DB13087D8364F3EA6509FD6F009A34F00426AF9E4F9FA85CBBF1F' then 'wBTC' else 'OSMO' end as b_currency
from osmosis.core.fact_liquidity_provider_actions left join prices on date_trunc('hour', block_timestamp) = hour and token='Wrapped Bitcoin'
where pool_id=712
group by date, b_currency),
eth as (select date_trunc('hour', block_timestamp) as date,
sum(case when action='pool_joined' then case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then price * amount/pow(10, DECIMAL) else (amount/pow(10, DECIMAL)) end end) as added_liquidity_to_weth,
sum(case when action='pool_exited' then case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then price * (-1 * amount/pow(10, DECIMAL)) else (-1 * amount/pow(10, DECIMAL)) end end) as withdraw_liquidity_from_weth,
case when currency='ibc/EA1D43981D5C9A1C4AAEA9C23BB1D4FA126BA9BC7020A25E0AE4AA841EA25DC5' then 'wETH' else 'OSMO' end as e_currency
from osmosis.core.fact_liquidity_provider_actions left join prices on date_trunc('hour', block_timestamp) = hour and token='Wrapped Ether'
where pool_id=704
group by date, e_currency)
select e.date, added_liquidity_to_wbtc as "WBTC/OSMO provided", withdraw_liquidity_from_wbtc as "WBTC/OSMO removed",
added_liquidity_to_weth as "WETH/OSMO provided", withdraw_liquidity_from_weth as "WETH/OSMO removed", b_currency, e_currency
from eth e left join btc using(date)
-- select * from osmosis.core.fact_msg_attributes where msg_type='coinbase' limit 100
Run a query to Download Data