nitsLP Providers over time
Updated 2022-12-14
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
›
⌄
with prices as
(
SELECT date(recorded_hour) as day, id, avg(open) as avg_price
from crosschain.core.fact_hourly_prices
where id ilike 'polkadot' or id ilike 'osmosis'
GROUP by 1 , 2 ),
enter as
(SELECT *, case when CURRENcy = 'usomo' then 'osmosis' else 'polkadot' end as id_
from osmosis.core.fact_liquidity_provider_actions a
where pool_id [0] = '773'
and tx_status = 'SUCCEEDED' and (action = 'pool_joined' or action = 'pool_exited')
)
-- SELECT *, amt_joined - amt_exit as net_amount , case when c = 'uosmo' then net_amount*1.5 else net_amount*5.3 end as net_val,
-- case when c = 'uosmo' then 'Osmo' else 'DOT' end as name,
-- case when c = 'uosmo' then amt_joined*1.5 else amt_joined*5.3 end as net_val_joined,
-- case when c = 'uosmo' then amt_exit*1.5 else amt_exit*5.3 end as net_val_exit
-- from
-- (SELECT * from enter join exit_
-- on c = currency)
SELECT date(block_timestamp) as day, action, currency, count(DISTINCT tx_id) as total_txs,
COUNT(DISTINCT liquidity_provider_address) as lp_providers,
sum(amount/pow(10,decimal) * avg_price) as amt_usd,
case when currency = 'uosmo' then 'Osmo' else 'DOT' end as name
from enter
join prices on id_ =id and date(block_timestamp) = day
GROUP by 1,2 ,3
-- LIMIT 199
Run a query to Download Data