MoeOsmosis LP Stats
Updated 2023-02-10
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
›
⌄
with prices as
(
select
RECORDED_HOUR::date as days , SYMBOL,CURRENCY,
avg(price) as prices
from
osmosis.core.ez_prices
group by 1 ,2,3
)
,fin as (
select
b.*,prices,AMOUNT/pow(10,decimal) as AMOUNTs ,SYMBOL,
AMOUNTs*prices as AMOUNT_usd
from
osmosis.core.fact_liquidity_provider_actions b,prices p
where
block_timestamp::date = days and b.CURRENCY = p.CURRENCY
and AMOUNT is not null
)
-- , pools as (select
-- MODULE,POOL_ID,c.SYMBOL as SYMBOL1,cc.SYMBOL as SYMBOL2,concat(SYMBOL1,'/',SYMBOL2) as pool_Assets
-- from
-- osmosis.core.dim_liquidity_pools p
-- join prices c on p.ASSETS[0]:asset_address = c.currency
-- join prices cc on p.ASSETS[1]:asset_address = cc.currency )
select
'Osmosis LP Stats - timeframe as input' as type ,
round((sum(case when action = 'pool_joined' then AMOUNT_usd end )),2) as "deposit $",
round((sum(case when action = 'pool_exited' then AMOUNT_usd end )),2) as "withdraw $",
"deposit $"-"withdraw $" as net_usd,
Run a query to Download Data