sum_borrowed_amount_usd,Liquidation_Treshold, ----5)this is to calculate health factor of each address correpond to its collateral

(sum_col_amount_usd-Liquidation_treshold)/(sum_borrowed_amount_usd) as Health_Factor

(with data1 as(with col as (

depositor_address,symbol as collateral ,tx_id,supplied_usd ---1) This is to identify the collateral depositor and the collateralized token

FROM aave.deposits

),

bor as(

tx_id,borrowed_usd ----- 2) this is to gather the tx_id and the borrowing data

FROM aave.borrows

select depositor_address as user,collateral, sum(supplied_usd) as sum_col_amount_usd,

sum(borrowed_usd) as sum_borrowed_amount_usd,count(bor.tx_id) as n_txn

from COL

inner join bor on bor.tx_id=col.tx_id ---3) This is to combine the step 1) and 2)

where borrowed_usd is not null

group by 1,2 order by 4 desc

select user,collateral, sum_col_amount_usd, sum_borrowed_amount_usd,

case when collateral='WETH' then '0.825'

when collateral='USDC' then '0.85'

when collateral='WBTC' then '0.75'

when collateral='LINK' then '0.75'

when collateral='DAI' then '0.80'

when collateral='YFI' then '0.55'

when collateral='AAVE' then '0.65'

when collateral='UNI' then '0.65'

when collateral='MKR' then '0.65' --4) this is to assign the liquidation treshold to each collateral token