with borrow as ( select from_address, sum(value_eth) as value_eth from( select from_address, count(1) as amount, avg(price_eth) as recent_price_eth, amount*recent_price_eth as value_eth from ethereum.core.ez_nft_transfers as tb1 join ethereum.core.fact_transactions as tb2 on tb1.tx_hash = tb2.tx_hash join( select nft_address, avg(price) as price_eth from ethereum.core.ez_nft_sales where block_timestamp::date>=CURRENT_DATE-30 group by 1 ) as tb3 on tb1.nft_address = tb3.nft_address -- where tx_hash = '0x0fb6ebb597ad052b3052adc5bc2942ce153164a121608851812ac4a04638e6a1' where origin_function_signature in ('0x35611f7e','0x9c748eff') and from_address = nft_from_address and to_address = '0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88' group by 1 ) group by 1 ), repay as ( select from_address, sum(value_eth) as value_eth from( select from_address, count(1) as amount, avg(price_eth) as recent_price_eth, amount*recent_price_eth as value_eth from ethereum.core.ez_nft_transfers as tb1 join ethereum.core.fact_transactions as tb2 on tb1.tx_hash = tb2.tx_hash left join( select nft_address, avg(price) as price_eth from ethereum.core.ez_nft_sales where block_timestamp::date>=CURRENT_DATE-30 group by 1 ) as tb3 on tb1.nft_address = tb3.nft_address where origin_function_signature in ('0x89cbe656','0x633d2a19') and from_address = nft_to_address and to_address = '0x3b968d2d299b895a5fcf3bba7a64ad0f566e6f88' group by 1 ) group by 1 ), repay_eth as ( select origin_from_address, sum(amount) as value_eth from ethereum.core.ez_eth_transfers where ORIGIN_TO_ADDRESS = lower('0x3B968D2D299B895A5Fcf3BBa7A64ad0F566e6F88') and origin_function_signature in ('0x89cbe656','0x633d2a19') group by 1 ), borrow_eth as ( select origin_from_address, sum(amount) as value_eth from ethereum.core.ez_eth_transfers where ORIGIN_TO_ADDRESS = lower('0x3B968D2D299B895A5Fcf3BBa7A64ad0F566e6F88') and origin_function_signature in ('0x35611f7e','0x9c748eff') group by 1 ), all_collateral as ( select tb1.from_address as address, case when tb2.value_eth>0 then tb1.value_eth - tb2.value_eth else tb1.value_eth end as collateral_eth from borrow as tb1 left join repay as tb2 on tb1.from_address = tb2.from_address where collateral_eth>0.1 ), all_debt as ( select tb1.origin_from_address as address, case when tb2.value_eth>0 then tb1.value_eth-tb2.value_eth else tb1.value_eth end as debt from borrow_eth as tb1 left join repay_eth as tb2 on tb1.origin_from_address = tb2.origin_from_address where debt>0.1 ), all_util as ( select tb1.*, case when debt>0 then debt else 0 end as debt_eth, debt_eth/collateral_eth*100 as utilization_rates from all_collateral as tb1 left join all_debt as tb2 on tb1.address = tb2.address order by 4 desc) select '0' as label, count(1) as address from all_util where utilization_rates = 0 union ( select '1-10' as label, count(1) as address from all_util where utilization_rates>=1 and utilization_rates<10 ) union ( select '10-20' as label, count(1) as address from all_util where utilization_rates>=10 and utilization_rates<20 ) union ( select '20-30' as label, count(1) as address from all_util where utilization_rates>=20 and utilization_rates<30 ) union ( select '30-40' as label, count(1) as address from all_util where utilization_rates>=30 and utilization_rates<40 ) union ( select '40-50' as label, count(1) as address from all_util where utilization_rates>=40 and utilization_rates<50 ) union ( select '50-60' as label, count(1) as address from all_util where utilization_rates>=50 and utilization_rates<60 ) union ( select '60-70' as label, count(1) as address from all_util where utilization_rates>=60 and utilization_rates<70 ) union ( select '70-80' as label, count(1) as address from all_util where utilization_rates>=70 and utilization_rates<80 ) union ( select '80-90' as label, count(1) as address from all_util where utilization_rates>=80 and utilization_rates<90 ) union ( select '90-100' as label, count(1) as address from all_util where utilization_rates>=90 and utilization_rates<100 ) union ( select '>100' as label, count(1) as address from all_util where utilization_rates>=100 )

BendDAO Dashboard