scottincryptoCompound volatility v2
Updated 2021-06-05
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
37
38
›
⌄
with week_stats as (
select
date_trunc('week',block_hour) as week,
underlying_symbol,
avg(token_price) as week_price_avg,
stddev(token_price) as week_price_stddev,
avg(borrow_apy) as week_apy_avg,
stddev(borrow_apy) as week_apy_stddev
from compound.market_stats
where block_hour > getdate() - interval'60 days'
group by 1,2
order by underlying_symbol, week
),
day_stats as(
select
date_trunc('day',block_hour) as date,
date_trunc('week',block_hour) as week,
underlying_symbol,
avg(token_price) as day_price,
avg(borrow_apy) as day_apy
from compound.market_stats
where block_hour > getdate() - interval'60 days'
group by 1, 2, 3
order by underlying_symbol, date
)
select
d.date,
d.underlying_symbol,
d.day_price,
d.day_apy,
w.week_price_avg,
(d.day_price-w.week_price_avg)/w.week_price_stddev as price_z_score
from day_stats d join week_stats w on (d.week = w.week and d.underlying_symbol = w.underlying_symbol)
-- where h.underlying_symbol = 'CRV'
order by d.underlying_symbol, d.date
Run a query to Download Data