--Component 1: all addresses that have made at least one Anchor borrow in the last 3 months with active_addresses as (select distinct sender as address from anchor.borrows where block_timestamp >= cast('2022-02-20' as date) - interval '3 months' and address is not null), --Component 2: Anchor collateral anchor_collateral as (select tx_id, block_timestamp, sender as address, currency, case when currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'bLUNA' when currency = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' then 'bETH' end as collateral, case when event_type = 'withdraw' then -amount else amount end as amount from anchor.collateral where block_timestamp <= '2022-02-20' and address in (select address from active_addresses)), anchor_liquidation as (select tx_id, block_timestamp, borrower as address, liquidated_currency as currency, case when liquidated_currency = 'terra1kc87mu460fwkqte29rquh4hc20m54fxwtsx7gp' then 'bLUNA' when liquidated_currency = 'terra1dzhzukyezv0etz22ud940z7adyv7xgcjkahuun' then 'bETH' end as collateral, (-1) * liquidated_amount as amount from anchor.liquidations where block_timestamp <= '2022-02-20' and address in (select address from active_addresses)), prices as (select date_trunc('day',block_timestamp) AS date, currency, symbol, avg(price_usd) AS price_usd from terra.oracle_prices where date = '2022-02-20' -- price on analysis date group by date, currency, symbol), collateral as ( select * from anchor_collateral union all select * from anchor_liquidation), collateral_agg as (select max(date_trunc('day', block_timestamp)) as date, address, currency, collateral, sum(amount) as amount from collateral group by address, currency, collateral), collateral_agg_usd as (select ca.*, p.price_usd, ca.amount * p.price_usd as amount_usd from collateral_agg ca left join prices p on p.currency = ca.currency), collateral_final as (select address, sum(amount_usd) as amount_usd -- sum all collaterals from collateral_agg_usd where amount_usd > 0 group by address), --Component 3: Anchor borrowings anchor_borrow as (select tx_id, block_timestamp, sender as address, amount_usd from anchor.borrows where block_timestamp <= '2022-02-20' and address in (select address from active_addresses)), anchor_repay as (select tx_id, block_timestamp, sender as address, (-1) * amount_usd from anchor.repay where block_timestamp <= '2022-02-20' and address in (select address from active_addresses)), borrow as ( select * from anchor_borrow union all select * from anchor_repay), borrow_agg as (select max(date_trunc('day', block_timestamp)) as date, address, sum(amount_usd) as amount_usd from borrow group by address), borrow_final as (select address, amount_usd as amount_usd from borrow_agg where amount_usd > 0), -- Aggregation agg_table as (select borrow.address, borrow.amount_usd as borrowed_value, collateral.amount_usd as collateral_value, (borrow.amount_usd/collateral.amount_usd) * 100 as loan_to_value from borrow_final borrow left join collateral_final collateral on collateral.address = borrow.address), final_table as (select * from agg_table where loan_to_value < 80), final_table_buckets as (select *, case when loan_to_value> 70 and loan_to_value <= 80 then '08) 70% to 80% LTV' when loan_to_value > 60 and loan_to_value <= 70 then '07) 60% to 70% LTV' when loan_to_value > 50 and loan_to_value <= 60 then '06) 50% to 60% LTV' when loan_to_value > 40 and loan_to_value <= 50 then '05) 40% to 50% LTV' when loan_to_value> 30 and loan_to_value <= 40 then '04) 30% to 40% LTV' when loan_to_value > 20 and loan_to_value <= 30 then '03) 20% to 30% LTV' when loan_to_value > 10 and loan_to_value <= 20 then '02) 10% to 20% LTV' when loan_to_value > 0 and loan_to_value <= 10 then '01) 0% to 10% LTV' end as ltv_buckets from final_table) select ltv_buckets, count(address) as frequency, count(address) * 100.0 / sum(count(address)) over() as proportion_of_address from final_table_buckets group by ltv_buckets

Anchor Borrowers