binhachonLUNA & Drawdowns - recovery period #2
    Updated 2022-04-11
    with max_price as (
    select
    date_trunc('day', block_timestamp) as time,
    block_timestamp,
    price_usd as max_price,
    row_number() over (partition by time order by price_usd desc) as rank
    from terra.oracle_prices
    where symbol = 'LUNA'
    qualify rank = 1
    ),
    percent_drop as (
    select
    time,
    max_price,
    price_usd as min_price,
    100 * (max_price - min_price) / max_price as percent_drop
    from (
    select
    time,
    max_price,
    price_usd
    from terra.oracle_prices
    inner join max_price on (time = date_trunc('day', terra.oracle_prices.block_timestamp) and terra.oracle_prices.block_timestamp >= max_price.block_timestamp)
    where symbol = 'LUNA'
    )
    qualify row_number() over (partition by time order by price_usd) = 1
    ),
    Anchor_liquidation as (
    select
    date_trunc('day', block_timestamp) as time,
    sum(liquidated_amount) as liquidated_amount,
    sum(liquidated_amount_usd) as liquidated_amount_usd,
    sum(liquidated_amount_usd) / sum(liquidated_amount) as avg_liquidated_price
    from anchor.liquidations
    where liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp'
    group by 1
    Run a query to Download Data