Ez Lending Borrows

-- Daily borrowing volume by protocol
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT tx_hash) AS borrow_txns,
    COUNT(DISTINCT borrower) AS unique_borrowers,
    SUM(amount_usd) AS volume_usd
FROM <blockchain_name>.defi.ez_lending_borrows
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;

-- Top borrowed assets analysis
SELECT 
    token_symbol,
    token_address,
    COUNT(*) AS borrow_count,
    SUM(amount) AS total_borrowed,
    SUM(amount_usd) AS total_borrowed_usd,
    AVG(amount_usd) AS avg_borrow_size_usd
FROM <blockchain_name>.defi.ez_lending_borrows
WHERE block_timestamp >= CURRENT_DATE - 7
    AND token_symbol IS NOT NULL
GROUP BY 1, 2
ORDER BY 5 DESC
LIMIT 20;

-- Wallet Specific Borrow Analysis
SELECT 
    b.borrower,
    b.token_address AS borrowed_token_address,
    b.token_symbol AS borrowed_token_symbol,
    DATE_TRUNC('week', b.block_timestamp) AS weekly_block_timestamp,
    SUM(b.amount) AS total_borrow_amount,
    SUM(b.amount_usd) AS total_borrow_usd,
    SUM(r.amount) AS total_repayment_amount,
    SUM(r.amount_usd) AS total_repayment_usd,
    SUM(b.amount) - SUM(r.amount) AS net_borrowed_amount,
    SUM(b.amount_usd) - SUM(r.amount_usd) AS net_borrowed_usd
FROM 
    <blockchain_name>.defi.ez_lending_borrows b
LEFT JOIN <blockchain_name>.defi.ez_lending_repayments r
    ON b.borrower = r.borrower
    AND b.token_address = r.token_address
WHERE 
    b.borrower = LOWER('<user_address>')
GROUP BY 1, 2, 3, 4

-- User borrowing patterns
WITH user_stats AS (
    SELECT 
        borrower,
        COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days,
        COUNT(DISTINCT platform) AS platforms_used,
        COUNT(DISTINCT token_address) AS assets_borrowed,
        SUM(amount_usd) AS total_borrowed_usd,
        AVG(amount_usd) AS avg_borrow_size
    FROM <blockchain_name>.defi.ez_lending_borrows
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN total_borrowed_usd < 1000 THEN '< $1K'
        WHEN total_borrowed_usd < 10000 THEN '$1K - $10K'
        WHEN total_borrowed_usd < 100000 THEN '$10K - $100K'
        ELSE '> $100K'
    END AS borrower_tier,
    COUNT(*) AS user_count,
    AVG(active_days) AS avg_active_days,
    AVG(platforms_used) AS avg_platforms,
    AVG(total_borrowed_usd) AS avg_total_borrowed
FROM user_stats
GROUP BY 1
ORDER BY 5 DESC;

-- Protocol market share
WITH protocol_volume AS (
    SELECT 
        platform,
        SUM(amount_usd) AS total_volume,
        COUNT(DISTINCT borrower) AS unique_users,
        COUNT(*) AS transaction_count
    FROM <blockchain_name>.defi.ez_lending_borrows
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    platform,
    total_volume,
    total_volume * 100.0 / SUM(total_volume) OVER () AS market_share_pct,
    unique_users,
    transaction_count,
    total_volume / transaction_count AS avg_borrow_size
FROM protocol_volume
ORDER BY total_volume DESC;