cypherosmosis stats weekly
Updated 2022-11-01
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
›
⌄
-- analyse trading activity for week of 28th vs weekly averages
-- analyse trading activity for 28th vs daily averages.
-- daily average should be :
-- 1) last 60 days
-- 2) last 60 days before May terra collapse.
-- analyse metrics for other tokens as well.
with data as (select
date_trunc('day', block_timestamp) as date,
count(distinct(tx_id)) as n_swaps,
count(distinct(trader)) as swappers,
sum(iff(from_currency = 'uosmo', from_amount/1e6, to_amount/1e6)) as osmo_amount
from osmosis.core.fact_swaps
where (from_currency = 'uosmo' or to_currency = 'uosmo')
group by date),
osmo_price as (select
date_trunc('day', recorded_at) as date,
avg(price) as avg_daily_price
from osmosis.core.dim_prices
where symbol = 'OSMO'
group by date),
joined as (select * from data
join osmo_price using (date)),
daily_data as (select *,
osmo_amount * avg_daily_price as volume_usd
from joined)
Run a query to Download Data