Ez Liquid Staking Withdrawals

-- Daily withdrawal patterns by protocol
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS withdrawal_txns,
    COUNT(DISTINCT staker) AS unique_unstakers,
    SUM(eth_amount) AS eth_withdrawn,
    SUM(eth_amount_usd) AS usd_withdrawn,
    AVG(eth_amount) AS avg_withdrawal_size
FROM defi.ez_liquid_staking_withdrawals
WHERE block_timestamp >= CURRENT_DATE - 30
    AND eth_amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 5 DESC;

-- Net staking flows (deposits vs withdrawals)
WITH daily_deposits AS (
    SELECT 
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(eth_amount) AS eth_deposited,
        COUNT(DISTINCT staker) AS depositors
    FROM defi.ez_liquid_staking_deposits
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
),
daily_withdrawals AS (
    SELECT 
        DATE_TRUNC('day', block_timestamp) AS date,
        platform,
        SUM(eth_amount) AS eth_withdrawn,
        COUNT(DISTINCT staker) AS withdrawers
    FROM defi.ez_liquid_staking_withdrawals
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
)
SELECT 
    COALESCE(d.date, w.date) AS date,
    COALESCE(d.platform, w.platform) AS platform,
    COALESCE(d.eth_deposited, 0) AS eth_deposited,
    COALESCE(w.eth_withdrawn, 0) AS eth_withdrawn,
    COALESCE(d.eth_deposited, 0) - COALESCE(w.eth_withdrawn, 0) AS net_eth_flow,
    COALESCE(d.depositors, 0) AS depositors,
    COALESCE(w.withdrawers, 0) AS withdrawers
FROM daily_deposits d
FULL OUTER JOIN daily_withdrawals w 
    ON d.date = w.date AND d.platform = w.platform
ORDER BY date DESC, ABS(net_eth_flow) DESC;

-- Staker holding period analysis
WITH staker_lifecycle AS (
    SELECT 
        d.staker,
        d.platform,
        d.block_timestamp AS deposit_time,
        MIN(w.block_timestamp) AS withdrawal_time,
        d.eth_amount AS deposit_amount,
        d.token_amount AS tokens_received
    FROM defi.ez_liquid_staking_deposits d
    LEFT JOIN defi.ez_liquid_staking_withdrawals w
        ON d.staker = w.staker 
        AND d.platform = w.platform
        AND d.token_address = w.token_address
        AND w.block_timestamp > d.block_timestamp
    WHERE d.eth_amount IS NOT NULL
    GROUP BY 1, 2, 3, 5, 6
)
SELECT 
    platform,
    COUNT(CASE WHEN withdrawal_time IS NOT NULL THEN 1 END) AS completed_cycles,
    COUNT(CASE WHEN withdrawal_time IS NULL THEN 1 END) AS still_staking,
    AVG(CASE 
        WHEN withdrawal_time IS NOT NULL 
        THEN EXTRACT(EPOCH FROM (withdrawal_time - deposit_time)) / 86400 
    END) AS avg_holding_days,
    PERCENTILE_CONT(0.5) WITHIN GROUP (
        ORDER BY CASE 
            WHEN withdrawal_time IS NOT NULL 
            THEN EXTRACT(EPOCH FROM (withdrawal_time - deposit_time)) / 86400 
        END
    ) AS median_holding_days
FROM staker_lifecycle
WHERE deposit_time >= CURRENT_DATE - 365
GROUP BY platform
ORDER BY completed_cycles DESC;

-- Exchange rate at withdrawal (profit/loss analysis)
SELECT 
    platform,
    token_symbol,
    DATE_TRUNC('week', block_timestamp) AS week,
    AVG(eth_amount / NULLIF(token_amount, 0)) AS avg_redemption_rate,
    MIN(eth_amount / NULLIF(token_amount, 0)) AS min_rate,
    MAX(eth_amount / NULLIF(token_amount, 0)) AS max_rate,
    COUNT(*) AS withdrawals
FROM defi.ez_liquid_staking_withdrawals
WHERE token_amount > 0 
    AND eth_amount > 0
    AND block_timestamp >= CURRENT_DATE - 90
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC;

-- Large withdrawals monitoring (potential de-risking)
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    staker,
    eth_amount,
    eth_amount_usd,
    token_symbol,
    token_amount,
    eth_amount / NULLIF(token_amount, 0) AS redemption_rate
FROM defi.ez_liquid_staking_withdrawals
WHERE eth_amount >= 100
    AND block_timestamp >= CURRENT_DATE - 3
ORDER BY eth_amount DESC;

-- Withdrawal pressure indicators
WITH hourly_flows AS (
    SELECT 
        DATE_TRUNC('hour', block_timestamp) AS hour,
        platform,
        SUM(eth_amount) AS hourly_withdrawals,
        COUNT(*) AS withdrawal_count,
        COUNT(DISTINCT staker) AS unique_withdrawers
    FROM defi.ez_liquid_staking_withdrawals
    WHERE block_timestamp >= CURRENT_DATE - 7
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
)
SELECT 
    platform,
    MAX(hourly_withdrawals) AS peak_hourly_withdrawal,
    AVG(hourly_withdrawals) AS avg_hourly_withdrawal,
    MAX(withdrawal_count) AS peak_withdrawal_count,
    STDDEV(hourly_withdrawals) AS withdrawal_volatility
FROM hourly_flows
GROUP BY platform
HAVING MAX(hourly_withdrawals) > 100
ORDER BY peak_hourly_withdrawal DESC;