with table_0 as (select to_date(block_timestamp) as date, sum(liquidated_amount) as liq_am, case when liq_am > 1000 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 ethereum.aave.ez_liquidations where upper(collateral_token_symbol) like '%ETH%' -- this includes both ETH and sETH group by date), eth_price as (select date_trunc('day', hour) as date, avg(price) as price_eth from ethereum.core.fact_hourly_token_prices where upper(token_address) = upper('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2') group by date ), eth_price_offset as (select DATEADD(DAY, 1, date) as date, price_eth as price_eth_offs from eth_price), final_table as (select a.date, price_eth, price_eth_offs, (price_eth - price_eth_offs) as price_change, (((price_eth/price_eth_offs)*100) - 100) as percent_diff, case when percent_diff > 0 then 'Gain' else 'Loss' end as gain_loss, c.liq_am, c.liq_type, c.liq_type_numeric from eth_price a left join eth_price_offset b on a.date = b.date left join table_0 c on a.date = c.date ), final_table_2 as ( select a.*, case when date = '2020-09-05' then 5 when date = '2020-11-07' then 4 when date = '2020-11-26' then 2 when date = '2021-01-11' then 1 when date = '2021-01-21' then 3 when date = '2021-01-22' then 1 when date = '2021-02-15' then 1 when date = '2021-02-20' then 41 when date = '2021-02-22' then 15 when date = '2021-02-23' then 1 when date = '2021-02-28' then 1 when date = '2021-04-07' then 1 when date = '2021-04-18' then 1 when date = '2021-04-22' then 5 when date = '2021-04-23' then 3 when date = '2021-05-12' then 167 when date = '2021-05-13' then 1 when date = '2021-05-16' then 109 when date = '2021-05-17' then 1 when date = '2021-05-19' then 80 when date = '2021-05-20' then 6 when date = '2021-05-21' then 5 when date = '2021-05-23' then 1 when date = '2021-06-18' then 16 when date = '2021-06-22' then 1 when date = '2021-09-07' then 37 when date = '2021-09-10' then 2 when date = '2021-10-30' then 3 when date = '2021-11-16' then 9 when date = '2021-12-04' then 1 when date = '2021-12-13' then 3 when date = '2022-01-05' then datediff(day, date, CURRENT_DATE) when date = '2022-01-06' then datediff(day, date, CURRENT_DATE) when date = '2022-01-07' then 5 when date = '2022-01-08' then 4 when date = '2022-01-10' then 1 when date = '2022-01-21' then 14 when date = '2022-01-22' then 4 when date = '2022-01-24' then 1 when date = '2022-02-24' then 1 when date = '2022-05-09' then datediff(day, date, CURRENT_DATE) when date = '2022-05-11' then datediff(day, date, CURRENT_DATE) when date = '2022-05-12' then 1 when date = '2022-06-10' then 59 when date = '2022-06-11' then 47 when date = '2022-06-12' then 36 when date = '2022-06-13' then 34 when date = '2022-06-14' then 11 when date = '2022-06-15' then 6 when date = '2022-06-18' then 1 else 0 end as days_to_recover, case when date = '2020-09-05' then 4 when date = '2020-11-07' then 67 when date = '2020-11-26' then 2 when date = '2021-01-11' then 1 when date = '2021-01-21' then 2 when date = '2021-01-22' then 3 when date = '2021-02-15' then 1 when date = '2021-02-20' then 0 when date = '2021-02-22' then 2 when date = '2021-02-23' then 3 when date = '2021-02-28' then 8 when date = '2021-04-07' then 4 when date = '2021-04-18' then 3 when date = '2021-04-22' then 0 when date = '2021-04-23' then 1 when date = '2021-05-12' then 0 when date = '2021-05-13' then 1 when date = '2021-05-16' then 4 when date = '2021-05-17' then 5 when date = '2021-05-19' then 7 when date = '2021-05-20' then 8 when date = '2021-05-21' then 9 when date = '2021-05-23' then 11 when date = '2021-06-18' then 37 when date = '2021-06-22' then 41 when date = '2021-09-07' then 118 when date = '2021-09-10' then 121 when date = '2021-10-30' then 1 when date = '2021-11-16' then 7 when date = '2021-12-04' then 25 when date = '2021-12-13' then 34 when date = '2022-01-05' then 57 when date = '2022-01-06' then 58 when date = '2022-01-07' then 59 when date = '2022-01-08' then 60 when date = '2022-01-10' then 62 when date = '2022-01-21' then 73 when date = '2022-01-22' then 74 when date = '2022-01-24' then 76 when date = '2022-02-24' then 107 when date = '2022-05-09' then 181 when date = '2022-05-11' then 183 when date = '2022-05-12' then 184 when date = '2022-06-10' then 213 when date = '2022-06-11' then 214 when date = '2022-06-12' then 215 when date = '2022-06-13' then 216 when date = '2022-06-14' then 217 when date = '2022-06-15' then 218 when date = '2022-06-18' then 221 else 0 end as days_since_ath, case when date = '2020-09-05' then 0 when date = '2020-11-07' then 63 when date = '2020-11-26' then 19 when date = '2021-01-11' then 46 when date = '2021-01-21' then 10 when date = '2021-01-22' then 1 when date = '2021-02-15' then 24 when date = '2021-02-20' then 5 when date = '2021-02-22' then 2 when date = '2021-02-23' then 1 when date = '2021-02-28' then 5 when date = '2021-04-07' then 38 when date = '2021-04-18' then 11 when date = '2021-04-22' then 4 when date = '2021-04-23' then 1 when date = '2021-05-12' then 19 when date = '2021-05-13' then 1 when date = '2021-05-16' then 3 when date = '2021-05-17' then 1 when date = '2021-05-19' then 2 when date = '2021-05-20' then 1 when date = '2021-05-21' then 1 when date = '2021-05-23' then 2 when date = '2021-06-18' then 26 when date = '2021-06-22' then 4 when date = '2021-09-07' then 77 when date = '2021-09-10' then 3 when date = '2021-10-30' then 50 when date = '2021-11-16' then 17 when date = '2021-12-04' then 18 when date = '2021-12-13' then 9 when date = '2022-01-05' then 23 when date = '2022-01-06' then 1 when date = '2022-01-07' then 1 when date = '2022-01-08' then 1 when date = '2022-01-10' then 2 when date = '2022-01-21' then 11 when date = '2022-01-22' then 1 when date = '2022-01-24' then 2 when date = '2022-02-24' then 31 when date = '2022-05-09' then 74 when date = '2022-05-11' then 2 when date = '2022-05-12' then 1 when date = '2022-06-10' then 29 when date = '2022-06-11' then 1 when date = '2022-06-12' then 1 when date = '2022-06-13' then 1 when date = '2022-06-14' then 1 when date = '2022-06-15' then 1 when date = '2022-06-18' then 3 else 0 end as days_since_last_liquidation_event from final_table a WHERE LIQ_TYPE = 'Liq_cascade'), 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

Open Analytics Bounty: ETH