cypherosmosis avg before terra collapse
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('hour', block_timestamp) as hour,
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')
and hour >= '2022-3-8'
and hour < '2022-5-7'
group by hour),
osmo_price as (select
date_trunc('hour', recorded_at) as hour,
avg(price) as avg_hourly_price
from osmosis.core.dim_prices
where symbol = 'OSMO'
group by hour),
joined as (select * from data
join osmo_price using (hour)),
hourly_data as (select *,
osmo_amount * avg_hourly_price as volume_usd