with table_0 as (select date_trunc('day', block_timestamp) as date, case when liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'Luna' else 'Ethereum' end as Liquidated_currency_translated, sum(liquidated_amount) as liq_am, case when liq_am > 100000 then 'Liq_cascade' else 'Nonliq_cascade' end as liq_type, case when liq_type = 'Liq_cascade' then 1 else -1 end as liq_type_numeric from anchor.liquidations where date_trunc('day', block_timestamp) >= '2021-01-01' and liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' group by 1, 2), luna_price as (select date_trunc('day', block_timestamp) as date, avg(price_usd) as price_luna from terra.oracle_prices where symbol = 'LUNA' and date_trunc('day', block_timestamp) >= '2021-01-01' group by 1), luna_price_offset as (select DATEADD(DAY, 1, date) as date, price_luna as price_luna_offs from luna_price), bluna_liquidations as (select date_trunc('day', block_timestamp) as date, case when liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'Luna' else 'Ethereum' end as Liquidated_currency_translated, sum(liquidated_amount) as liquidated_amount from anchor.liquidations where date_trunc('day', block_timestamp) >= '2021-01-01' and liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' group by 1, 2), final_table as (select a.date, price_luna, price_luna_offs, (price_luna - price_luna_offs) as price_change, (((price_luna/price_luna_offs)*100) - 100) as percent_diff, case when percent_diff > 0 then 'Gain' else 'Loss' end as gain_loss, c.liquidated_amount, d.liq_type from luna_price a left join luna_price_offset b on a.date = b.date left join bluna_liquidations c on a.date = c.date left join table_0 d on a.date = d.date ), final_table_2 as (select a.*, case when a.date = '2021-03-24' then 6 when a.date = '2021-04-07' then 20 when a.date = '2021-04-12' then 2 when a.date = '2021-04-18' then 8 when a.date = '2021-05-13' then 91 when a.date = '2021-05-19' then 85 when a.date = '2021-05-20' then 76 when a.date = '2021-05-21' then 70 when a.date = '2021-05-22' then 68 when a.date = '2021-05-23' then 48 when a.date = '2021-06-04' then 36 when a.date = '2021-06-21' then 9 when a.date = '2021-09-07' then 4 when a.date = '2021-09-20' then 5 when a.date = '2021-10-12' then 9 when a.date = '2021-10-27' then 8 when a.date = '2021-11-16' then 15 when a.date = '2021-12-04' then 2 when a.date = '2022-01-05' then 56 when a.date = '2022-01-08' then 4 when a.date = '2022-01-21' then 40 when a.date = '2022-01-22' then 36 when a.date = '2022-01-27' then 30 when a.date = '2022-01-28' then 27 when a.date = '2022-04-11' then 0 else 0 end as Days_to_recover, case when a.date = '2021-03-24' then 2 when a.date = '2021-04-07' then 16 when a.date = '2021-04-12' then 21 when a.date = '2021-04-18' then 56 when a.date = '2021-05-13' then 51 when a.date = '2021-05-19' then 58 when a.date = '2021-05-20' then 59 when a.date = '2021-05-21' then 60 when a.date = '2021-05-22' then 61 when a.date = '2021-05-23' then 62 when a.date = '2021-06-04' then 74 when a.date = '2021-06-21' then 91 when a.date = '2021-09-07' then 8 when a.date = '2021-09-20' then 8 when a.date = '2021-10-12' then 7 when a.date = '2021-10-27' then 22 when a.date = '2021-11-16' then 6 when a.date = '2021-12-04' then 1 when a.date = '2022-01-05' then 9 when a.date = '2022-01-08' then 12 when a.date = '2022-01-21' then 25 when a.date = '2022-01-22' then 26 when a.date = '2022-01-27' then 31 when a.date = '2022-01-28' then 32 when a.date = '2022-04-11' then 5 else 0 end as Days_since_ATH, case when a.date = '2021-03-24' then 0 when a.date = '2021-04-07' then 14 when a.date = '2021-04-12' then 5 when a.date = '2021-04-18' then 6 when a.date = '2021-05-13' then 31 when a.date = '2021-05-19' then 6 when a.date = '2021-05-20' then 1 when a.date = '2021-05-21' then 1 when a.date = '2021-05-22' then 1 when a.date = '2021-05-23' then 1 when a.date = '2021-06-04' then 12 when a.date = '2021-06-21' then 17 when a.date = '2021-09-07' then 95 when a.date = '2021-09-20' then 13 when a.date = '2021-10-12' then 22 when a.date = '2021-10-27' then 15 when a.date = '2021-11-16' then 20 when a.date = '2021-12-04' then 18 when a.date = '2022-01-05' then 32 when a.date = '2022-01-08' then 3 when a.date = '2022-01-21' then 13 when a.date = '2022-01-22' then 1 when a.date = '2022-01-27' then 5 when a.date = '2022-01-28' then 1 when a.date = '2022-04-11' then 73 else 0 end as days_since_last_liquidation_event from final_table a where liq_type = 'Liq_cascade' and gain_loss = 'Loss'), last_final as (select case when abs(percent_diff) < 5 then 'Between 0 and 5' when abs(percent_diff) > 5 and abs(percent_diff) < 7.5 then 'Between 5 and 7.5' when abs(percent_diff) > 7.5 and abs(percent_diff) < 10 then 'Between 7.5 and 10' when abs(percent_diff) > 10 and abs(percent_diff) < 15 then 'Between 10 and 15' when abs(percent_diff) > 15 and abs(percent_diff) < 20 then 'Between 15 and 20' when abs(percent_diff) > 20 then 'Higher than 20' else 'NA' end as Percent_difference_aggregated, case when abs(percent_diff) < 5 then 1 when abs(percent_diff) > 5 and abs(percent_diff) < 7.5 then 2 when abs(percent_diff) > 7.5 and abs(percent_diff) < 10 then 3 when abs(percent_diff) > 10 and abs(percent_diff) < 15 then 4 when abs(percent_diff) > 15 and abs(percent_diff) < 20 then 5 when abs(percent_diff) > 20 then 6 else null end as Percent_difference_aggregated_numeric, avg(days_to_recover) over (order by Percent_difference_aggregated_numeric) as Average_days_to_recover, avg(days_since_ath) over (order by Percent_difference_aggregated_numeric) as Average_days_since_ath, avg(days_since_last_liquidation_event) over (order by Percent_difference_aggregated_numeric) as Average_days_since_last_liquidation_event from final_table_2) select Percent_difference_aggregated, Percent_difference_aggregated_numeric, Average_days_to_recover, Average_days_since_ath, Average_days_since_last_liquidation_event from last_final group by 1, 2, 3 ,4, 5

Terra Bounties - LUNA & Drawdowns