Ez Ink Balances Native Daily

SELECT 
    block_date,
    blockchain,
    native_token,
    address,
    balance,
    balance_usd,
    LAG(balance) OVER (
        PARTITION BY address, blockchain 
        ORDER BY block_date
    ) AS prev_balance,
    balance - LAG(balance) OVER (
        PARTITION BY address, blockchain 
        ORDER BY block_date
    ) AS daily_change
FROM crosschain.balances.ez_balances_native_daily
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
    AND block_date >= CURRENT_DATE - 30
    AND balance > 0
    AND blockchain = 'ethereum'
ORDER BY block_date DESC;

SELECT 
    block_date,
    blockchain,
    native_token,
    COUNT(DISTINCT address) AS total_holders,
    COUNT(DISTINCT CASE WHEN balance >= 1 THEN address END) AS holders_1_plus,
    COUNT(DISTINCT CASE WHEN balance >= 10 THEN address END) AS holders_10_plus,
    COUNT(DISTINCT CASE WHEN balance >= 100 THEN address END) AS holders_100_plus,
    COUNT(DISTINCT CASE WHEN balance >= 1000 THEN address END) AS holders_1000_plus,
    SUM(balance) AS total_native_tracked,
    AVG(balance) AS avg_balance,
    MEDIAN(balance) AS median_balance,
    MAX(balance) AS max_balance
FROM crosschain.balances.ez_balances_native_daily
WHERE block_date >= CURRENT_DATE - 90
    AND balance > 0
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3
ORDER BY 1 DESC;

-- Track large native asset holders and their balance changes
WITH whale_balances AS (
    SELECT 
        blockchain,
        native_token,
        address,
        block_date,
        balance,
        balance_usd,
        LAG(balance) OVER (
            PARTITION BY address, blockchain 
            ORDER BY block_date
        ) AS prev_balance,
        LAG(balance_usd) OVER (
            PARTITION BY address, blockchain 
            ORDER BY block_date
        ) AS prev_balance_usd
    FROM crosschain.balances.ez_balances_native_daily
    WHERE balance >= 1000  -- Focus on large holders
        AND block_date >= CURRENT_DATE - 7
        AND blockchain = 'ethereum'
),
whale_changes AS (
    SELECT 
        *,
        balance - prev_balance AS balance_change,
        balance_usd - prev_balance_usd AS balance_change_usd,
        CASE 
            WHEN prev_balance > 0 
            THEN ((balance - prev_balance) / prev_balance) * 100 
            ELSE NULL 
        END AS pct_change
    FROM whale_balances
    WHERE prev_balance IS NOT NULL
)
SELECT 
    block_date,
    blockchain,
    native_token,
    address,
    balance,
    balance_change,
    balance_change_usd,
    pct_change,
    CASE 
        WHEN balance_change > 100 THEN 'Large Increase'
        WHEN balance_change > 10 THEN 'Moderate Increase'
        WHEN balance_change < -100 THEN 'Large Decrease'
        WHEN balance_change < -10 THEN 'Moderate Decrease'
        ELSE 'Stable'
    END AS change_category
FROM whale_changes
WHERE ABS(balance_change) > 5  -- Only show meaningful changes
ORDER BY ABS(balance_change_usd) DESC
LIMIT 100;

-- Analyze the distribution of native asset balances
SELECT 
    block_date,
    blockchain,
    native_token,
    CASE 
        WHEN balance < 0.001 THEN 'Dust (<0.001)'
        WHEN balance < 0.01 THEN 'Very Small (0.001-0.01)'
        WHEN balance < 0.1 THEN 'Small (0.01-0.1)'
        WHEN balance < 1 THEN 'Medium (0.1-1)'
        WHEN balance < 10 THEN 'Large (1-10)'
        WHEN balance < 100 THEN 'Very Large (10-100)'
        WHEN balance < 1000 THEN 'Whale (100-1000)'
        ELSE 'Super Whale (1000+)'
    END AS balance_category,
    COUNT(DISTINCT address) AS address_count,
    SUM(balance) AS total_balance,
    SUM(balance_usd) AS total_balance_usd,
    AVG(balance) AS avg_balance,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY balance) AS median_balance
FROM crosschain.balances.ez_balances_native_daily
WHERE block_date = CURRENT_DATE - 1
    AND balance > 0
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3, 4
ORDER BY 1 DESC, 2,
    CASE balance_category
        WHEN 'Dust (<0.001)' THEN 1
        WHEN 'Very Small (0.001-0.01)' THEN 2
        WHEN 'Small (0.01-0.1)' THEN 3
        WHEN 'Medium (0.1-1)' THEN 4
        WHEN 'Large (1-10)' THEN 5
        WHEN 'Very Large (10-100)' THEN 6
        WHEN 'Whale (100-1000)' THEN 7
        WHEN 'Super Whale (1000+)' THEN 8
    END;

-- Track total portfolio value changes for top holders across blockchains
SELECT 
    block_date,
    blockchain,
    native_token,
    COUNT(DISTINCT address) AS tracked_addresses,
    SUM(balance_usd) AS total_portfolio_value,
    AVG(balance_usd) AS avg_portfolio_value,
    SUM(balance) AS total_native_balance,
    LAG(SUM(balance_usd)) OVER (PARTITION BY blockchain ORDER BY block_date) AS prev_total_value,
    (SUM(balance_usd) - LAG(SUM(balance_usd)) OVER (PARTITION BY blockchain ORDER BY block_date)) AS daily_value_change,
    CASE 
        WHEN LAG(SUM(balance_usd)) OVER (PARTITION BY blockchain ORDER BY block_date) > 0
        THEN ((SUM(balance_usd) - LAG(SUM(balance_usd)) OVER (PARTITION BY blockchain ORDER BY block_date)) / LAG(SUM(balance_usd)) OVER (PARTITION BY blockchain ORDER BY block_date)) * 100
        ELSE NULL
    END AS daily_pct_change
FROM crosschain.balances.ez_balances_native_daily
WHERE block_date >= CURRENT_DATE - 30
    AND balance_usd >= 10000  -- Focus on significant holders
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 2;

SELECT 
    blockchain,
    native_token,
    block_date,
    COUNT(DISTINCT address) AS daily_holders,
    SUM(balance) AS total_native_balance,
    SUM(balance_usd) AS total_balance_usd,
    AVG(balance_usd) AS avg_balance_usd,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY balance_usd) AS p95_balance_usd,
    COUNT(DISTINCT CASE WHEN balance_usd >= 10000 THEN address END) AS whale_count
FROM crosschain.balances.ez_balances_native_daily
WHERE block_date >= CURRENT_DATE - 30
    AND balance > 0
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3 DESC;