shreyash-5873LUNA Price Buckets 29th of July
Updated 2021-08-04
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
›
⌄
with price_ranges as (select
date(block_timestamp) as block_date,
symbol,
avg(price_usd) as avg_price_usd,
max(price_usd) as max_price_usd,
min(price_usd) as min_price_usd,
stddev(price_usd) as volatility,
stddev(price_usd) / avg(price_usd) * 100 as normalised_volaility_percentage
from terra.oracle_prices
where symbol = 'LUNA'
and block_date >= date('2021-07-17')
and block_date <= date('2021-07-30')
group by 1, 2),
buckets as (
select
m.block_timestamp,
case when price_usd >= min_price_usd and price_usd < (min_price_usd + avg_price_usd) / 2 then 0
when price_usd >= (min_price_usd + avg_price_usd) / 2 and price_usd < avg_price_usd then 1
when price_usd >= avg_price_usd and price_usd < (avg_price_usd + max_price_usd) / 2 then 2
when price_usd >= (avg_price_usd + max_price_usd) / 2 and price_usd <= max_price_usd then 3
end as bucket
from terra.oracle_prices m
inner join price_ranges p
on date(m.block_timestamp) = p.block_date
where date(m.block_timestamp) = date('2021-07-29')
and m.symbol = 'LUNA')
select
date(block_timestamp),
bucket,
count(*)
from buckets
group by 1, 2
Run a query to Download Data