Ez Lending Liquidations

-- Daily liquidation volume and metrics
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS liquidation_count,
    COUNT(DISTINCT borrower) AS unique_borrowers_liquidated,
    SUM(amount_usd) AS total_debt_covered_usd,
    SUM(amount_usd) AS total_collateral_liquidated_usd,
    AVG(amount_usd / NULLIF(amount_usd, 0) - 1) * 100 AS avg_liquidation_bonus_pct
FROM <blockchain_name>.defi.ez_lending_liquidations
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;

-- Most liquidated asset pairs
SELECT 
    collateral_token,
    collateral_token_symbol,
    debt_token,
    debt_token_symbol,
    COUNT(*) AS liquidation_count,
    SUM(amount_usd) AS total_collateral_liquidated_usd,
    AVG(amount_usd) AS avg_liquidation_size_usd
FROM <blockchain_name>.defi.ez_lending_liquidations
WHERE block_timestamp >= CURRENT_DATE - 7
    AND collateral_token_symbol IS NOT NULL
    AND debt_token_symbol IS NOT NULL
GROUP BY 1, 2, 3, 4
ORDER BY 6 DESC
LIMIT 20;

-- Liquidator analysis
WITH liquidator_stats AS (
    SELECT 
        liquidator,
        COUNT(*) AS liquidations_performed,
        SUM(amount_usd) AS total_collateral_received_usd,
    FROM <blockchain_name>.defi.ez_lending_liquidations
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    CASE 
        WHEN liquidations_performed = 1 THEN 'Opportunistic'
        WHEN liquidations_performed <= 10 THEN 'Active'
        WHEN liquidations_performed <= 100 THEN 'Professional'
        ELSE 'Bot/High Frequency'
    END AS liquidator_type,
    COUNT(*) AS liquidator_count,
    SUM(total_collateral_received_usd) as total_collateral_received_usd
    SUM(liquidations_performed) AS total_liquidations
FROM liquidator_stats
GROUP BY 1
ORDER BY 3 DESC;

-- Large liquidations monitoring
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    borrower,
    liquidator,
    collateral_token_symbol,
    debt_token_symbol,
    amount_usd,
FROM <blockchain_name>.defi.ez_lending_liquidations
WHERE amount_usd > 10000
    AND block_timestamp >= CURRENT_DATE - 14
ORDER BY amount_usd DESC;

-- Borrower liquidation history
WITH borrower_liquidations AS (
    SELECT 
        borrower,
        COUNT(*) AS times_liquidated,
        COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS liquidation_days,
        SUM(amount_usd) AS total_collateral_lost_usd,
        ARRAY_AGG(DISTINCT platform) AS platforms_liquidated_on
    FROM <blockchain_name>.defi.ez_lending_liquidations
    WHERE block_timestamp >= CURRENT_DATE - 90
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT 
    times_liquidated,
    platforms_liquidated_on,
    COUNT(*) AS borrower_count,
    AVG(total_collateral_lost_usd) AS avg_loss_from_liquidation
FROM borrower_liquidations
GROUP BY 1, 2
ORDER BY 1, 2;