from terra.oracle_prices

where symbol = 'LUNA' and year(block_timestamp) in ('2021', '2022')

group by 1),

price as (select date_trunc('day', block_timestamp) as dt, avg(price_usd) as LUNA_price

from terra.oracle_prices

where symbol = 'LUNA' and year(block_timestamp) in ('2021', '2022')

group by 1),

perc as (select dt, luna_price, lag(LUNA_price) OVER (ORDER BY dt asc) as luna_price_before, percentage_drop

from raw

where percentage_drop < 0

order by 4 asc

limit 10),

lastt as (select a.dt, round(a.luna_price,2) as luna_price, round(a.luna_price_before,2) as luna_price_before, a.percentage_drop, b.dt as ddt, b.luna_price as same_level_price

from perc a

left join price b

where same_level_price > a.luna_price_before and ddt > a.dt)

select dt, concat('$',luna_price_before,' TO ','$', luna_price) as prices, percentage_drop, min(ddt) as level_price_dt, min(same_level_price) as level_price, datediff(day, dt, level_price_dt) as "Days Until Level Price"

from lastt

group by 1,2,3

order by percentage_drop asc