Skip to main content
Schema: ink.balances Table: ez_balances_native_daily Type: Base Table

​
What

This table provides daily native asset balance snapshots (ETH, AVAX, BNB, etc.) by making direct eth_getBalance RPC calls at the end of each day. It captures the current native asset balance for each address that has had native asset activity, providing a comprehensive view of native holdings with decimal adjustments and USD valuations where available. Historical native balances data available, starting from 2025-06-10.

​
Key Use Cases

  • Daily portfolio tracking and native asset balance monitoring
  • Historical native asset balance analysis and trend identification
  • Whale tracking and large holder distribution analysis
  • Daily balance snapshots for reporting and analytics
  • Native asset concentration analysis across addresses
  • Daily balance-based yield and return calculations for native assets
  • End-of-day balance reconciliation and accounting

​
Important Relationships

  • Join with fact_blocks: Use block_number for block metadata and timestamps
  • Join with dim_labels: Use address for entity identification and categorization
  • Join with ez_prices_hourly: USD valuations already included but can be refreshed
  • Join with ez_balances_erc20_daily: Compare with ERC20 token daily balances
  • Join with ez_native_transfers: Compare daily balances with transfer activity
  • Join with core__fact_traces: Analyze native asset movement patterns

​
Commonly-used Fields

  • address: The account address holding the native asset balance
  • balance: Native asset balance at end of day, decimal adjusted to standard units
  • balance_usd: USD value of the native asset balance at end of day
  • balance_raw: Raw balance in smallest unit (Wei) without decimal adjustment
  • balance_precise: Precise decimal-adjusted balance as string
  • balance_hex: Hexadecimal balance as returned by eth_getBalance
  • decimals: Number of decimal places (always 18 for native assets)
  • block_date: The date for which this balance snapshot was taken

​
Sample queries

Daily Native Asset Holdings by Address 
SELECT
    block_date,
    address,
    balance,
    balance_usd,
    LAG(balance) OVER (
        PARTITION BY address
        ORDER BY block_date
    ) AS prev_balance,
    balance - LAG(balance) OVER (
        PARTITION BY address
        ORDER BY block_date
    ) AS daily_change
FROM <blockchain_name>.balances.ez_balances_native_daily
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
    AND block_date >= CURRENT_DATE - 30
    AND balance > 0
ORDER BY block_date DESC;
Native Asset Holder Distribution Trends 
SELECT
    block_date,
    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 <blockchain_name>.balances.ez_balances_native_daily
WHERE block_date >= CURRENT_DATE - 90
    AND balance > 0
GROUP BY 1
ORDER BY 1 DESC;
Whale Activity Monitoring 
-- Track large native asset holders and their balance changes
WITH whale_balances AS (
    SELECT
        address,
        block_date,
        balance,
        balance_usd,
        LAG(balance) OVER (
            PARTITION BY address
            ORDER BY block_date
        ) AS prev_balance,
        LAG(balance_usd) OVER (
            PARTITION BY address
            ORDER BY block_date
        ) AS prev_balance_usd
    FROM <blockchain_name>.balances.ez_balances_native_daily
    WHERE balance >= 1000  -- Focus on large holders
        AND block_date >= CURRENT_DATE - 7
),
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,
    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;
Daily Balance Distribution Analysis 
-- Analyze the distribution of native asset balances
SELECT
    block_date,
    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 <blockchain_name>.balances.ez_balances_native_daily
WHERE block_date = CURRENT_DATE - 1
    AND balance > 0
GROUP BY 1, 2
ORDER BY 1 DESC,
    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;
Portfolio Value Evolution 
-- Track total portfolio value changes for top holders
SELECT
    block_date,
    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 (ORDER BY block_date) AS prev_total_value,
    (SUM(balance_usd) - LAG(SUM(balance_usd)) OVER (ORDER BY block_date)) AS daily_value_change,
    CASE
        WHEN LAG(SUM(balance_usd)) OVER (ORDER BY block_date) > 0
        THEN ((SUM(balance_usd) - LAG(SUM(balance_usd)) OVER (ORDER BY block_date)) / LAG(SUM(balance_usd)) OVER (ORDER BY block_date)) * 100
        ELSE NULL
    END AS daily_pct_change
FROM <blockchain_name>.balances.ez_balances_native_daily
WHERE block_date >= CURRENT_DATE - 30
    AND balance_usd >= 10000  -- Focus on significant holders
GROUP BY 1
ORDER BY 1 DESC;

​
Columns

Column NameData TypeDescription
BLOCK_NUMBERNUMBERSequential counter representing the position of a block in the blockchain since genesis (block 0).
Key Facts:
  • Immutable once finalized
  • Primary ordering mechanism for blockchain data
  • Increments by 1 for each new block
  • Used as a proxy for time in many analyses
Usage in Queries: 
-- Recent data
WHERE block_number >= (SELECT MAX(block_number) - 1000 FROM fact_blocks)

-- Historical analysis
WHERE block_number BETWEEN 15000000 AND 16000000

-- Join across tables
JOIN <blockchain_name>.core.fact_event_logs USING (block_number)
Important: Block numbers are chain-specific. Block 15000000 on Ethereum ≠ block 15000000 on Polygon. | | BLOCK_DATE | DATE | The date for which this balance snapshot represents the end-of-day native asset balance. Example: ‘2023-12-15’ | | ADDRESS | TEXT | The account address whose native asset balance is recorded in this daily snapshot. Example: ‘0x1234567890123456789012345678901234567890’ | | DECIMALS | NUMBER | | | BALANCE_HEX | TEXT | Hexadecimal representation of the native asset balance as returned by the eth_getBalance RPC call. Example: ‘0x3b9aca00’ | | BALANCE_RAW | NUMBER | Native asset balance in the smallest unit (Wei) without decimal adjustment, as returned by eth_getBalance. Example: 1000000000000000000 | | BALANCE_PRECISE | TEXT | Native asset balance with proper decimal adjustment, returned as a string to preserve precision. Example: ‘1.000000000000000000’ | | BALANCE | FLOAT | Native asset balance with decimal adjustment converted to a float for easier mathematical operations. Example: 1.0 | | BALANCE_USD | FLOAT | USD value of the native asset balance at the end of the day, calculated using hourly price data. Example: 2500.75 | | EZ_BALANCES_NATIVE_DAILY_ID | TEXT | Primary key - unique identifier for each row ensuring data integrity. Format: Usually VARCHAR containing composite key generated using MD5 hash of the relevant columns. Example: MD5(block_number, tx_hash, trace_index) Usage:
  • Deduplication in incremental loads
  • Join operations for data quality checks
  • Troubleshooting specific records
Important: Implementation varies by table - check table-specific documentation. | | INSERTED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp when the record was first added to the Flipside database. Format: TIMESTAMP_NTZ Use Cases:
  • Data freshness monitoring
  • Incremental processing markers
  • Debugging data pipeline issues
  • SLA tracking
Query Example: 
-- Check data latency
SELECT
    DATE_TRUNC('hour', block_timestamp) as block_hour,
    DATE_TRUNC('hour', inserted_timestamp) as insert_hour,
    AVG(TIMESTAMPDIFF('minute', block_timestamp, inserted_timestamp)) as avg_latency_minutes
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2;
``` |
| MODIFIED_TIMESTAMP | TIMESTAMP_NTZ | UTC timestamp of the most recent update to this record.

**Format**: TIMESTAMP_NTZ

**Triggers for Updates**:
- Data corrections
- Enrichment additions
- Reprocessing for accuracy
- Schema migrations

**Monitoring Usage**:
```sql
-- Recently modified records
SELECT *
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
AND modified_timestamp >= CURRENT_DATE - 1;

-- Data quality tracking
SELECT
    DATE(modified_timestamp) as mod_date,
    COUNT(*) as records_updated,
    COUNT(DISTINCT block_number) as blocks_affected
FROM <blockchain_name>.core.fact_transactions
WHERE modified_timestamp > inserted_timestamp
GROUP BY 1
ORDER BY 1 DESC;
``` |