SELECT

BLOCK_TIMESTAMP,

tx_id,

split(EVENT_DATA:nftType, '.') [2] as Collection,

( event_data:borrower::string) AS Borrower,

( event_data:lender::string) AS Lender,

(event_data:repaymentAmount::float) AS Loan_Amount

FLOW.CORE.FACT_EVENTS

WHERE EVENT_CONTRACT = ('A.5c57f79c6694797f.Flowty')

AND EVENT_TYPE in ('FundingRepaid', 'FundingSettled')

AND TX_SUCCEEDED = TRUE

,

mid as (

select

Borrower ,

count(distinct tx_id) as loans

from base

group by 1

select

case

when loans = 1 then '1 loan'

when loans > 1 and loans <= 5 then '2-5 loans'

when loans > 5 and loans <= 10 then '6-10 loans'

when loans > 10 then 'over 10 loans'

end as tier ,

count (distinct Borrower) as wallets