warriorgem11
Updated 2023-06-09
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
›
⌄
with ev_price_tbl as (
select date(RECORDED_AT) as pdate, avg(price) as price
from osmosis.core.dim_prices
where symbol = 'ATOM'
group by pdate
)
select date_trunc('day', block_timestamp) as date,
count(distinct TX_ID) as swaps,
count(distinct TRADER) as swappers,
sum(case when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as usd_vol,
avg(case when FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then FROM_AMOUNT/1e6 * price else TO_AMOUNT/1e6 * price end) as avg_usd_vol,
sum(usd_vol) over (order by date) as cumu_usd_vol,
sum(swaps) over (order by date) as cumu_swaps,
price
from osmosis.core.fact_swaps
join ev_price_tbl on ev_price_tbl.pdate = date(block_timestamp)
where (FROM_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' OR TO_CURRENCY = 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' )
group by date, price
Run a query to Download Data