Ez Balances Native

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS balance_changes,
    COUNT(DISTINCT address) AS unique_addresses,
    SUM(ABS(net_balance)) AS total_balance_moved,
    SUM(CASE WHEN net_balance > 0 THEN net_balance ELSE 0 END) AS total_increases,
    SUM(CASE WHEN net_balance < 0 THEN ABS(net_balance) ELSE 0 END) AS total_decreases
FROM <blockchain_name>.balances.ez_balances_native
WHERE block_timestamp >= CURRENT_DATE - 30
    AND net_balance != 0
GROUP BY 1
ORDER BY 1 DESC;

-- Track how a specific address's balance changed over time
SELECT
    block_timestamp,
    tx_hash,
    pre_balance,
    post_balance,
    net_balance,
    pre_balance_usd,
    post_balance_usd,
    SUM(net_balance) OVER (
        PARTITION BY address
        ORDER BY block_number, tx_position
        ROWS UNBOUNDED PRECEDING
    ) AS running_balance_change
FROM <blockchain_name>.balances.ez_balances_native
WHERE address = LOWER('0x1234567890123456789012345678901234567890')
    AND block_timestamp >= CURRENT_DATE - 30
ORDER BY block_timestamp DESC;

-- Analyze how smart contract interactions affect user balances
WITH contract_interactions AS (
    SELECT
        b.address,
        b.tx_hash,
        b.net_balance,
        b.block_timestamp,
        t.to_address AS contract_interacted
    FROM <blockchain_name>.balances.ez_balances_native b
    JOIN <blockchain_name>.core.fact_transactions t USING (tx_hash)
    WHERE b.net_balance != 0
        AND t.to_address IN (SELECT address FROM dim_contracts)
        AND b.block_timestamp >= CURRENT_DATE - 7
)
SELECT
    contract_interacted,
    dc.name AS contract_name,
    COUNT(*) AS balance_changes,
    COUNT(DISTINCT address) AS unique_users,
    SUM(CASE WHEN net_balance > 0 THEN net_balance ELSE 0 END) AS total_gains,
    SUM(CASE WHEN net_balance < 0 THEN ABS(net_balance) ELSE 0 END) AS total_losses,
    AVG(ABS(net_balance)) AS avg_balance_change
FROM contract_interactions c
LEFT JOIN <blockchain_name>.core.dim_contracts dc ON c.contract_interacted = dc.address
GROUP BY 1, 2
HAVING COUNT(*) > 10
ORDER BY total_gains + total_losses DESC
LIMIT 50;

-- Analyze the distribution of balance changes by magnitude
SELECT
    CASE
        WHEN ABS(net_balance) < 0.01 THEN 'Dust (<0.01)'
        WHEN ABS(net_balance) < 0.1 THEN 'Small (0.01-0.1)'
        WHEN ABS(net_balance) < 1 THEN 'Medium (0.1-1)'
        WHEN ABS(net_balance) < 10 THEN 'Large (1-10)'
        WHEN ABS(net_balance) < 100 THEN 'Very Large (10-100)'
        ELSE 'Whale (100+)'
    END AS balance_change_category,
    COUNT(*) AS change_count,
    COUNT(DISTINCT address) AS unique_addresses,
    SUM(ABS(net_balance)) AS total_volume,
    AVG(ABS(net_balance)) AS avg_magnitude
FROM <blockchain_name>.balances.ez_balances_native
WHERE block_timestamp >= CURRENT_DATE - 1
    AND net_balance != 0
GROUP BY 1
ORDER BY
    CASE balance_change_category
        WHEN 'Dust (<0.01)' THEN 1
        WHEN 'Small (0.01-0.1)' THEN 2
        WHEN 'Medium (0.1-1)' THEN 3
        WHEN 'Large (1-10)' THEN 4
        WHEN 'Very Large (10-100)' THEN 5
        WHEN 'Whale (100+)' THEN 6
    END;

-- 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)

-- Time-based filtering (most efficient)
WHERE block_timestamp >= DATEADD('day', -7, CURRENT_TIMESTAMP)

-- Hourly aggregations
DATE_TRUNC('hour', block_timestamp) AS hour

-- UTC date extraction
DATE(block_timestamp) AS block_date

-- 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;
``` |