nitsLUNA drawdowns percent chart
Updated 2022-02-25
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
›
⌄
with prices as (SELECT block_timestamp, price_usd from terra.oracle_prices where symbol = 'LUNA'
and date(block_timestamp)> CURRENT_DATE -366),
data as (SELECT hour,price,price_usd, rn from
(SELECT * from (SELECT hour,price, ROW_NUMBER() over (order by hour) as rn from ethereum.token_prices_hourly_v2
where symbol = 'ETH' and date(hour)> CURRENT_DATE -366)
inner join prices
on hour = block_timestamp))
-- limit 100
-- SELECT count(*) as total_cases,
(SELECT hour,rn,(price-token_price)/token_price*100 as percent_eth_change,(price_usd-luna_price)/luna_price*100 as percent_luna_change,
case when percent_eth_change < '0' and percent_luna_change > '2' then '1' else '0' end as luna_reversal,
case when percent_eth_change < '0' and percent_luna_change < '0' and percent_luna_change<percent_eth_change then '1' else '0' end as luna_drawdown,
case when percent_eth_change < '0' and percent_luna_change < '0' then '1' else '0' end as drawdown_samples,
case when percent_eth_change > '0' and percent_luna_change > '0' then '1' else '0' end as upward_samples,
case when percent_eth_change > '0' and percent_luna_change > '0' and percent_luna_change>percent_eth_change then '1' else '0' end as luna_upward
from
(SELECT hour as hr ,price as token_price,price_usd as luna_price, rn as rn1 from
(SELECT * from (SELECT hour,price, ROW_NUMBER() over (order by hour) as rn from ethereum.token_prices_hourly_v2
where symbol = 'ETH' and date(hour)> CURRENT_DATE -366)
inner join prices
on hour = block_timestamp))
inner join data on rn =rn1+1)
ORDER by rn
-- limit 100
Run a query to Download Data