b.address_name,

a.contract_Address,

a.n_loans_given

FROM

(

SELECT

contract_address,

--event_inputs:from as wallet,

sum(

CASE WHEN event_name = 'LogAddCollateral' THEN event_inputs : share WHEN event_name = 'LogRemoveCollateral' THEN -1 * event_inputs : share end

)/ pow(10, 8) as borrow,

count(

DISTINCT CASE WHEN event_name = 'LogAddCollateral' then tx_hash end

) as n_loans_given,

rank() over(

order by

n_loans_given desc

) as rank

FROM

bsc.core.fact_event_logs

WHERE

event_name IN (

'LogAddCollateral', 'LogRemoveCollateral'

)

GROUP BY

contract_address

) a

LEFT JOIN bsc.core.dim_labels b ON a.contract_Address = b.address

WHERE

b.address_name is not null

ORDER BY

a.n_loans_given DESC