granadohoNumber of Days Above and Below $52
Updated 2022-02-26
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
›
⌄
with closed_price as (
select
*
from (
select
symbol,
date(block_timestamp) as date,
block_timestamp,
price_usd,
dense_rank() over ( partition by date order by block_timestamp desc) as index
from terra.oracle_prices
where symbol = 'LUNA'
)
where index = 1 and EXTRACT(HOUR FROM block_timestamp) = 23 and date >= '2021-11-01'
order by date
)
select
a.currency,
a.average_price,
a.number_of_days_above_52,
b.number_of_days_below_52
from (
select
symbol as currency,
count(distinct date) as number_of_days_above_52,
avg(price_usd) as average_price
from closed_price
where price_usd > 52
group by currency
) a
inner join (
select
symbol as currency,
count(distinct date) as number_of_days_below_52
from closed_price
Run a query to Download Data