WITH BORROWS AS ( SELECT sender as borrower, min(date(block_timestamp)) as first_borrow_date, max(date(block_timestamp)) as last_borrow_date, count(distinct tx_id) as borrow_counts, sum(amount) as total_ust_borrow, avg(amount) as avg_ust_borrow FROM anchor.borrows GROUP BY 1 ), REPAYS AS ( SELECT sender as repayer, min(date(block_timestamp)) as first_repay_date, max(date(block_timestamp)) as last_repay_date, count(distinct tx_id) as repay_count, sum(amount) as total_ust_repay, avg(amount) as avg_ust_repay FROM anchor.repay GROUP BY 1 ), FINAL AS ( SELECT DATEDIFF(day, first_borrow_date, first_repay_date) AS days_first_repay, DATEDIFF(day, first_borrow_date, last_repay_date) AS days_last_repay, round(total_ust_borrow - ifnull(total_ust_repay,0), 1) as balance, -- if negative, withdrawn all case when borrower is not null and repayer is null then 'Borrowed, Never Repay' when borrower is not null and repayer is not null then 'Borrowed and Repayed' ELSE NULL end as user_type, case when user_type = 'Borrowed, Never Repay' THEN DATEDIFF(day, last_borrow_date, getdate()) else null end AS days, -- days since last repay * FROM BORROWS a LEFT JOIN REPAYS b on a.borrower = repayer ), FINAL_FINAL AS ( SELECT *, ifnull(total_ust_repay,0)/total_ust_borrow*100 as percent_repaid, CASE WHEN percent_repaid >= 100 THEN 'a. => 100% Repaid' -- Total redeemed / total depsoited WHEN percent_repaid >80 AND percent_repaid < 100 THEN 'b. 80-99.9% Repaid' WHEN percent_repaid >50 AND percent_repaid <= 80 THEN 'c. 50-80% Repaid' WHEN percent_repaid >30 AND percent_repaid <= 50 THEN 'd. 30-50% Repaid' WHEN percent_repaid >10 AND percent_repaid <= 30 THEN 'e. 10-30% Repaid' WHEN percent_repaid > 0 AND percent_repaid <= 10 THEN 'f. <10% Repaid' WHEN percent_repaid = 0 THEN 'g. 0% Repaid' ELSE NULL END AS "Borrowed % Repaid", 1 as count, CASE WHEN total_ust_borrow > 0 AND total_ust_borrow <= 1000 THEN 'a. Shrimps <1k' -- Not account for withdrawals WHEN total_ust_borrow > 1000 AND total_ust_borrow <= 10000 THEN 'b. Crab 1k-10k' WHEN total_ust_borrow > 10000 AND total_ust_borrow <= 50000 THEN 'c. Fish 10k-50k' WHEN total_ust_borrow > 50000 AND total_ust_borrow <= pow(10,5) THEN 'd. Dolphin 50k-100k' WHEN total_ust_borrow > pow(10,5) AND total_ust_borrow <= pow(10,6) THEN 'e. Shark 100k-1M' WHEN total_ust_borrow > pow(10,6) AND total_ust_borrow <= pow(10,8) THEN 'f. Whale >1M-100M' WHEN total_ust_borrow> pow(10,8) THEN 'g. Humpback >100M' ELSE NULL END AS Tier FROM FINAL ) SELECT tier, "Borrowed % Repaid", count(distinct borrower) as distinct_borrowers, sum(total_ust_repay) as "Total Repaid", sum(total_ust_borrow) as "Total Borrowed", sum(balance) as "Total Balance", median(balance) as median_balance, -- acounting for redeems sum(balance) as total_ust, -- account for redeems median(days) as "Median Days for non-repayers", -- from first borrow avg(days) as "Avg Days for non-repayers", -- from first borrow median(days_first_repay) as "Median Days to 1st Repay", avg(days_first_repay) as "Avg Days to 1st Repay", median(days_last_repay) as "Median Days to Last Repay", avg(days_last_repay) as "Avg Days to Last Repay" FROM FINAL_FINAL GROUP BY 1,2 ORDER BY 1,2

Anchor Borrow Duration