WITH BORROWS AS ( SELECT sender as borrower, tx_id, date(block_timestamp) as date, block_timestamp, lag(block_timestamp) ignore nulls over(partition by borrower ORDER BY block_timestamp ASC) as block_timestamp_prev, datediff('hour', block_timestamp_prev, block_timestamp) as hours_between_loan, hours_between_loan/24 as days_between_loan, amount FROM anchor.borrows WHERE datediff('day', block_timestamp, getdate()) between 0 and 90 ORDER BY 2 ), BORROW_STATS AS ( SELECT borrower, 1 as "Count", avg(days_between_loan) as "Avg Days Between Loan", median(days_between_loan) as "Median Days Between Loan", avg(hours_between_loan) as "Avg Hours Between Loan", count(distinct tx_id) as "Borrow Count", sum(amount) as "Total Loan" FROM BORROWS GROUP BY 1 ORDER BY "Total Loan" DESC ), REPAY AS ( SELECT sender as repayer, tx_id, date(block_timestamp) as date, block_timestamp, lag(block_timestamp) ignore nulls over(partition by repayer ORDER BY block_timestamp ASC) as block_timestamp_prev, datediff('hour', block_timestamp_prev, block_timestamp) as hours_between_repay, hours_between_repay/24 as days_between_repay, amount FROM anchor.repay WHERE datediff('day', block_timestamp, getdate()) between 0 and 90 ORDER BY 2 ), REPAY_STATS AS ( SELECT repayer, avg(days_between_repay) as "Avg Days Between Repay", median(days_between_repay) as "Median Days Between Repay", avg(hours_between_repay) as "Avg Hours Between Repay", count(distinct tx_id) as "Repay Count", sum(amount) as "Total Repay" FROM REPAY GROUP BY 1 ORDER BY "Total Repay" DESC ), FINAL AS ( SELECT *, Case when "Total Repay" IS NULL THEN 'Yet to repay' ELSE 'Repaid' end as "Repaid?", round(ifnull("Total Repay",0)/"Total Loan"*100, 0) as "% Loan Repaid", case when "% Loan Repaid" > 120 then 120 -- round everything to 120% when "% Loan Repaid" < 120 then "% Loan Repaid" end as "% Loan Repaid (Rounded)", CASE WHEN "% Loan Repaid" >= 100 THEN 'a. => 100% Repaid' -- Total redeemed / total depsoited WHEN "% Loan Repaid" >80 AND "% Loan Repaid" < 100 THEN 'b. 80-99.9% Repaid' WHEN "% Loan Repaid" >50 AND "% Loan Repaid" <= 80 THEN 'c. 50-80% Repaid' WHEN "% Loan Repaid" >30 AND "% Loan Repaid" <= 50 THEN 'd. 30-50% Repaid' WHEN "% Loan Repaid" >10 AND "% Loan Repaid" <= 30 THEN 'e. 10-30% Repaid' WHEN "% Loan Repaid" > 0 AND "% Loan Repaid" <= 10 THEN 'f. <10% Repaid' WHEN "% Loan Repaid" = 0 THEN 'g. 0% Repaid' ELSE NULL END AS "Borrowed % Repaid", CASE WHEN "Total Loan" > 0 AND "Total Loan" <= 1000 THEN 'a. Shrimps <1k' -- Not account for withdrawals WHEN "Total Loan" > 1000 AND "Total Loan" <= 10000 THEN 'b. Crab 1k-10k' WHEN "Total Loan" > 10000 AND "Total Loan" <= 50000 THEN 'c. Fish 10k-50k' WHEN "Total Loan" > 50000 AND "Total Loan" <= 100000 THEN 'd. Dolphin 50k-100k' WHEN "Total Loan" > 100000 AND "Total Loan" <= 1000000 THEN 'e. Shark 100k-1M' WHEN "Total Loan" > 1000000 THEN 'f. Whale >1M' ELSE NULL END AS "Borrow Tier", "Total Loan"-"Total Repay" as "Balance" FROM BORROW_STATS LEFT JOIN REPAY_STATS ON repayer = borrower ) SELECT "Borrow Tier", "Borrowed % Repaid", count(distinct borrower) as "Counts", sum("Total Loan") as "Total Loans", sum("Total Repay") as "Total Paid", sum("Balance") as "Total Balance", avg("Avg Days Between Repay") as "Avg Days" FROM FINAL GROUP BY 1,2

PSI Emissions and Buy-Sell Pressure