Ez Lending Repayments

-- Daily repayment volume and metrics
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS repayment_count,
    COUNT(DISTINCT borrower) AS unique_borrowers,
    SUM(amount_usd) AS total_repaid_usd,
    AVG(amount_usd) AS avg_repayment_size_usd
FROM <blockchain_name>.defi.ez_lending_repayments
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;

-- Loan duration analysis
WITH loan_lifecycles AS (
    SELECT 
        b.borrower,
        b.platform,
        b.token_symbol,
        b.block_timestamp AS borrow_time,
        MIN(r.block_timestamp) AS first_repayment_time,
        SUM(b.amount_usd) AS borrowed_usd,
        SUM(r.amount_usd) AS total_repaid_usd
    FROM <blockchain_name>.defi.ez_lending_borrows b
    INNER JOIN <blockchain_name>.defi.ez_lending_repayments r
        ON b.borrower = r.borrower
        AND b.platform = r.platform
        AND b.token_address = r.token_address
        AND r.block_timestamp > b.block_timestamp
    WHERE b.block_timestamp >= CURRENT_DATE - 90
        AND b.amount_usd IS NOT NULL
        AND r.amount_usd IS NOT NULL
    GROUP BY 1, 2, 3, 4
)
SELECT 
    platform,
    token_symbol,
    AVG(DATEDIFF('day', borrow_time, first_repayment_time)) AS avg_days_to_first_repayment,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY DATEDIFF('day', borrow_time, first_repayment_time)) AS median_days,
    COUNT(*) AS loan_count,
    AVG(total_repaid_usd / NULLIF(borrowed_usd, 0) - 1) * 100 AS avg_interest_paid_pct
FROM loan_lifecycles
GROUP BY 1, 2
HAVING COUNT(*) > 10
ORDER BY 3;

-- Repayment patterns by user segment
WITH user_repayment_stats AS (
    SELECT 
        borrower,
        COUNT(*) AS repayment_count,
        SUM(amount_usd) AS total_repaid_usd,
        AVG(amount_usd) AS avg_repayment_size,
        COUNT(DISTINCT DATE_TRUNC('month', block_timestamp)) AS active_months,
        COUNT(DISTINCT token_address) AS unique_assets_repaid
    FROM <blockchain_name>.defi.ez_lending_repayments
    WHERE block_timestamp >= CURRENT_DATE - 180
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN total_repaid_usd < 1000 THEN 'Micro (<$1K)'
        WHEN total_repaid_usd < 10000 THEN 'Small ($1K-$10K)'
        WHEN total_repaid_usd < 100000 THEN 'Medium ($10K-$100K)'
        WHEN total_repaid_usd < 1000000 THEN 'Large ($100K-$1M)'
        ELSE 'Whale (>$1M)'
    END AS borrower_segment,
    COUNT(*) AS borrower_count,
    AVG(repayment_count) AS avg_repayments_per_user,
    AVG(avg_repayment_size) AS avg_repayment_size,
    AVG(active_months) AS avg_active_months
FROM user_repayment_stats
GROUP BY 1
ORDER BY 2 DESC;

-- Asset-specific repayment velocity
SELECT 
    token_symbol,
    platform,
    COUNT(*) AS repayment_transactions,
    COUNT(DISTINCT borrower) AS unique_repayers,
    SUM(amount_usd) AS total_usd_repaid,
    AVG(amount_usd) AS avg_repayment_usd,
    SUM(amount_usd) / COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS daily_velocity_usd
FROM <blockchain_name>.defi.ez_lending_repayments
WHERE block_timestamp >= CURRENT_DATE - 30
    AND token_symbol IS NOT NULL
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
HAVING COUNT(*) > 50
ORDER BY 8 DESC;

-- Large repayments monitoring
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    borrower,
    payer,
    token_symbol,
    amount_usd,
    CASE WHEN borrower = payer THEN 'Self' ELSE 'Third-party' END AS repayment_type
FROM <blockchain_name>.defi.ez_lending_repayments
WHERE amount_usd > 500000
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount_usd DESC;