Ez Liquid Staking Deposits

-- Daily liquid staking deposits by protocol
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT tx_hash) AS deposit_txns,
    COUNT(DISTINCT staker) AS unique_stakers,
    SUM(eth_amount) AS eth_staked,
    SUM(eth_amount_usd) AS usd_staked,
    AVG(eth_amount) AS avg_stake_size
FROM defi.ez_liquid_staking_deposits
WHERE block_timestamp >= CURRENT_DATE - 30
    AND eth_amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

-- Protocol market share analysis
WITH protocol_totals AS (
    SELECT 
        platform,
        SUM(eth_amount) AS total_eth_staked,
        COUNT(DISTINCT staker) AS unique_stakers,
        COUNT(*) AS total_deposits
    FROM defi.ez_liquid_staking_deposits
    WHERE block_timestamp >= CURRENT_DATE - 90
        AND eth_amount IS NOT NULL
    GROUP BY platform
)
SELECT 
    platform,
    total_eth_staked,
    total_eth_staked * 100.0 / SUM(total_eth_staked) OVER () AS market_share_pct,
    unique_stakers,
    total_deposits,
    total_eth_staked / total_deposits AS avg_deposit_size
FROM protocol_totals
ORDER BY total_eth_staked DESC;

-- Staker behavior patterns
WITH staker_activity AS (
    SELECT 
        staker,
        COUNT(DISTINCT platform) AS protocols_used,
        COUNT(*) AS total_deposits,
        SUM(eth_amount) AS total_eth_staked,
        MIN(block_timestamp) AS first_stake,
        MAX(block_timestamp) AS last_stake,
        COUNT(DISTINCT DATE_TRUNC('month', block_timestamp)) AS active_months
    FROM defi.ez_liquid_staking_deposits
    WHERE eth_amount IS NOT NULL
    GROUP BY staker
)
SELECT 
    CASE 
        WHEN total_eth_staked < 1 THEN '< 1 ETH'
        WHEN total_eth_staked < 10 THEN '1-10 ETH'
        WHEN total_eth_staked < 32 THEN '10-32 ETH'
        WHEN total_eth_staked < 100 THEN '32-100 ETH'
        ELSE '100+ ETH'
    END AS staker_tier,
    COUNT(*) AS staker_count,
    AVG(total_deposits) AS avg_deposits_per_staker,
    AVG(protocols_used) AS avg_protocols_used,
    SUM(total_eth_staked) AS tier_total_eth
FROM staker_activity
GROUP BY staker_tier
ORDER BY MIN(total_eth_staked);

-- Exchange rate analysis (token received per ETH)
SELECT 
    platform,
    token_symbol,
    DATE_TRUNC('day', block_timestamp) AS date,
    AVG(token_amount / NULLIF(eth_amount, 0)) AS avg_exchange_rate,
    MIN(token_amount / NULLIF(eth_amount, 0)) AS min_rate,
    MAX(token_amount / NULLIF(eth_amount, 0)) AS max_rate,
    COUNT(*) AS sample_size
FROM defi.ez_liquid_staking_deposits
WHERE eth_amount > 0 
    AND token_amount > 0
    AND block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2, 3
ORDER BY 1, 3 DESC;

-- Large deposits monitoring (whale activity)
SELECT 
    block_timestamp,
    tx_hash,
    platform,
    staker,
    eth_amount,
    eth_amount_usd,
    token_symbol,
    token_amount,
    token_amount / NULLIF(eth_amount, 0) AS exchange_rate
FROM defi.ez_liquid_staking_deposits
WHERE eth_amount >= 100
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY eth_amount DESC;

-- Weekly staking momentum
WITH weekly_deposits AS (
    SELECT 
        DATE_TRUNC('week', block_timestamp) AS week,
        platform,
        SUM(eth_amount) AS weekly_eth_staked,
        COUNT(DISTINCT staker) AS unique_stakers
    FROM defi.ez_liquid_staking_deposits
    WHERE block_timestamp >= CURRENT_DATE - 84
        AND eth_amount IS NOT NULL
    GROUP BY 1, 2
)
SELECT 
    week,
    platform,
    weekly_eth_staked,
    LAG(weekly_eth_staked) OVER (PARTITION BY platform ORDER BY week) AS prev_week_eth,
    (weekly_eth_staked / NULLIF(LAG(weekly_eth_staked) OVER (PARTITION BY platform ORDER BY week), 0) - 1) * 100 AS week_over_week_pct,
    unique_stakers
FROM weekly_deposits
ORDER BY week DESC, weekly_eth_staked DESC;