Ez Lending Flashloans

-- Daily flash loan volume and fees
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS flashloan_count,
    SUM(flashloan_amount_usd) AS total_volume_usd,
    SUM(premium_amount_usd) AS total_fees_usd,
    AVG(premium_amount_usd / NULLIF(flashloan_amount_usd, 0) * 100) AS avg_fee_rate_pct
FROM <blockchain_name>.defi.ez_lending_flashloans
WHERE block_timestamp >= CURRENT_DATE - 30
    AND flashloan_amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 4 DESC;

-- Most flash loaned assets
SELECT 
    flashloan_token_symbol,
    flashloan_token,
    COUNT(*) AS loan_count,
    SUM(flashloan_amount) AS total_amount,
    SUM(flashloan_amount_usd) AS total_volume_usd,
    AVG(flashloan_amount_usd) AS avg_loan_size_usd,
    SUM(premium_amount_usd) AS total_fees_collected
FROM <blockchain_name>.defi.ez_lending_flashloans
WHERE block_timestamp >= CURRENT_DATE - 7
    AND flashloan_token_symbol IS NOT NULL
GROUP BY 1, 2
ORDER BY 5 DESC
LIMIT 20;

-- Flash loan user analysis
WITH flashloan_users AS (
    SELECT 
        initiator,
        COUNT(*) AS flashloan_count,
        COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days,
        COUNT(DISTINCT platform) AS protocols_used,
        SUM(flashloan_amount_usd) AS total_borrowed_usd,
        SUM(premium_amount_usd) AS total_fees_paid_usd
    FROM <blockchain_name>.defi.ez_lending_flashloans
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND flashloan_amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN flashloan_count = 1 THEN 'One-time User'
        WHEN flashloan_count <= 10 THEN 'Occasional User'
        WHEN flashloan_count <= 100 THEN 'Regular User'
        ELSE 'Power User'
    END AS user_category,
    COUNT(*) AS user_count,
    SUM(total_borrowed_usd) AS category_volume_usd,
    AVG(total_fees_paid_usd) AS avg_fees_per_user
FROM flashloan_users
GROUP BY 1
ORDER BY 3 DESC;

-- Large flash loans (potential arbitrage/liquidations)
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    initiator,
    target,
    flashloan_token_symbol,
    flashloan_amount_usd,
    premium_amount_usd,
    premium_amount_usd / NULLIF(flashloan_amount_usd, 0) * 100 AS fee_rate_pct
FROM <blockchain_name>.defi.ez_lending_flashloans
WHERE flashloan_amount_usd > 1000000
    AND block_timestamp >= CURRENT_DATE - 1
ORDER BY flashloan_amount_usd DESC;

-- Wallet-specific flash loan analysis
SELECT 
    initiator,
    platform,
    flashloan_token_symbol,
    COUNT(*) AS flashloan_count,
    SUM(flashloan_amount_usd) AS total_borrowed_usd,
    SUM(premium_amount_usd) AS total_fees_paid_usd,
    AVG(premium_amount_usd / NULLIF(flashloan_amount_usd, 0) * 100) AS avg_fee_rate_pct,
    MIN(block_timestamp) AS first_flashloan,
    MAX(block_timestamp) AS last_flashloan,
    COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days
FROM <blockchain_name>.defi.ez_lending_flashloans
WHERE initiator = LOWER('<wallet_address>')
    AND block_timestamp >= CURRENT_DATE - 30
    AND flashloan_amount_usd IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY total_borrowed_usd DESC;