Ez Native Transfers

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    SUM(amount_usd) AS total_usd,
    MAX(amount_usd) AS largest_transfer_usd
FROM <blockchain_name>.core.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount > 0
GROUP BY 1
ORDER BY 1 DESC;

SELECT
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    amount,
    amount_usd,
    origin_from_address,
    origin_to_address,
    identifier
FROM <blockchain_name>.core.ez_native_transfers
WHERE amount_usd > 1000000  -- Over $1M USD
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount_usd DESC
LIMIT 100;

WITH exchange_addresses AS (
    SELECT DISTINCT address
    FROM dim_labels
    WHERE label_type = 'exchange'
)
SELECT
    DATE_TRUNC('hour', block_timestamp) AS hour,
    CASE
        WHEN to_address IN (SELECT address FROM exchange_addresses) THEN 'Deposit'
        WHEN from_address IN (SELECT address FROM exchange_addresses) THEN 'Withdrawal'
    END AS transfer_type,
    COUNT(*) AS transfer_count,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd
FROM <blockchain_name>.core.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
    AND (to_address IN (SELECT address FROM exchange_addresses)
         OR from_address IN (SELECT address FROM exchange_addresses))
GROUP BY 1, 2
ORDER BY 1 DESC;

-- Compare external vs internal transfers
SELECT
    CASE
        WHEN identifier = '0' THEN 'External Transfer'
        ELSE 'Internal Transfer'
    END AS transfer_type,
    COUNT(*) AS count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    SUM(amount_usd) AS total_volume_usd
FROM <blockchain_name>.core.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1;

WITH contract_balances AS (
    SELECT
        to_address AS address,
        SUM(amount) AS inflows
    FROM <blockchain_name>.core.ez_native_transfers
    WHERE to_address IN (SELECT address FROM dim_contracts)
    GROUP BY 1
),
outflows AS (
    SELECT
        from_address AS address,
        SUM(amount) AS outflows
    FROM <blockchain_name>.core.ez_native_transfers
    WHERE from_address IN (SELECT address FROM dim_contracts)
    GROUP BY 1
)
SELECT
    c.address,
    dc.name AS contract_name,
    COALESCE(c.inflows, 0) - COALESCE(o.outflows, 0) AS net_balance,
    c.inflows,
    o.outflows
FROM contract_balances c
LEFT JOIN outflows o ON c.address = o.address
LEFT JOIN <blockchain_name>.core.dim_contracts dc ON c.address = dc.address
WHERE COALESCE(c.inflows, 0) - COALESCE(o.outflows, 0) > 100  -- Over 100 native tokens
ORDER BY net_balance DESC
LIMIT 50;

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

-- User activity analysis
SELECT from_address, COUNT(*) as tx_count
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 2 DESC;

-- New user detection
SELECT DISTINCT from_address
FROM <blockchain_name>.core.fact_transactions t1
WHERE NOT EXISTS (
    SELECT 1 FROM <blockchain_name>.core.fact_transactions t2
    WHERE t2.from_address = t1.from_address
    AND t2.block_number < t1.block_number
);
``` |
| ORIGIN_TO_ADDRESS | TEXT | The destination address for the transaction - either an EOA or contract address.

**Special Cases**:
- NULL: Contract creation transaction
- Contract address: Interacting with smart contract
- EOA address: Simple transfer or receiving funds

**Important Patterns**:
```sql
-- Contract deployments
WHERE to_address IS NULL

-- Popular contracts
SELECT to_address, COUNT(*) as interactions
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC;

-- Direct transfers only
WHERE to_address NOT IN (SELECT address FROM dim_contracts)

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