Ez Token Transfers

SELECT
    block_timestamp,
    tx_hash,
    symbol,
    from_address,
    to_address,
    amount,
    amount_usd,
    token_price,
    contract_address
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND amount_usd > 100000  -- Over $100k
    AND has_decimal = TRUE
    AND has_price = TRUE
ORDER BY amount_usd DESC
LIMIT 100;

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    symbol,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd,
    AVG(amount_usd) AS avg_transfer_usd
FROM <blockchain_name>.core.ez_token_transfers
WHERE symbol IN ('USDC', 'USDT', 'DAI', 'BUSD')
    AND block_timestamp >= CURRENT_DATE - 30
    AND has_decimal = TRUE
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

WITH dex_addresses AS (
    SELECT address
    FROM <blockchain_name>.core.dim_labels
    WHERE label_type = 'dex'
    AND label_subtype IN ('pool', 'router')
)
SELECT
    DATE_TRUNC('hour', block_timestamp) AS hour,
    symbol,
    CASE
        WHEN from_address IN (SELECT address FROM dex_addresses) THEN 'DEX Outflow'
        WHEN to_address IN (SELECT address FROM dex_addresses) THEN 'DEX Inflow'
    END AS flow_type,
    COUNT(*) AS transfers,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
    AND (from_address IN (SELECT address FROM dex_addresses)
         OR to_address IN (SELECT address FROM dex_addresses))
    AND symbol IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 6 DESC;

-- Most active token senders
SELECT
    from_address,
    COUNT(DISTINCT contract_address) AS tokens_sent,
    COUNT(DISTINCT DATE(block_timestamp)) AS active_days,
    COUNT(*) AS total_transfers,
    SUM(amount_usd) AS total_usd_sent
FROM <blockchain_name>.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND has_price = TRUE
    AND amount_usd > 10  -- Filter dust
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY total_usd_sent DESC
LIMIT 100;

WITH first_transfers AS (
    SELECT
        contract_address,
        symbol,
        MIN(block_timestamp) AS first_transfer,
        COUNT(*) AS transfer_count,
        COUNT(DISTINCT from_address) AS unique_senders,
        COUNT(DISTINCT to_address) AS unique_receivers
    FROM <blockchain_name>.core.ez_token_transfers
    WHERE block_timestamp >= CURRENT_DATE - 7
    GROUP BY 1, 2
    HAVING MIN(block_timestamp) >= CURRENT_DATE - 1
)
SELECT
    ft.*,
    dc.name AS token_name,
    dc.decimals
FROM first_transfers ft
LEFT JOIN <blockchain_name>.core.dim_contracts dc ON ft.contract_address = dc.address
ORDER BY transfer_count DESC;

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

-- Trace event execution flow
SELECT
    event_index,
    contract_address,
    topic_0,
    SUBSTRING(data, 1, 10) AS data_preview
FROM <blockchain_name>.core.fact_event_logs
WHERE tx_hash = '0xabc...'
ORDER BY event_index;
``` |
| FROM_ADDRESS | TEXT | The from address for the token transfer. This may or may not be the same as the origin_from_address.

Example: '0x1234567890123456789012345678901234567890' |
| TO_ADDRESS | TEXT | The to address for the token transfer. This may or may not be the same as the origin_to_address.

Example: '0xabcdefabcdefabcdefabcdefabcdefabcdefabcd' |
| CONTRACT_ADDRESS | TEXT | The contract address for the token transfer.

Example: '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' |
| TOKEN_STANDARD | TEXT | The token standard for the transfer, in this case always erc20.

Example: 'erc20' |
| TOKEN_IS_VERIFIED | BOOLEAN | Boolean flag indicating if the token is verified by the Flipside team.

Example: true |
| NAME | TEXT | Human-readable contract name from the name() function.

Example: 'USD Coin' |
| SYMBOL | TEXT | Token/contract symbol from the symbol() function.

Example: 'USDC' |
| DECIMALS | NUMBER | Number of decimal places for token amounts, read directly from the contract code.

Example: 6 |
| RAW_AMOUNT_PRECISE | TEXT | String representation of raw amount for precision preservation.

Example: '1000500000' |
| RAW_AMOUNT | FLOAT | Original token amount without decimal adjustment.

Example: 1000500000 |
| AMOUNT_PRECISE | TEXT | String representation of decimal-adjusted amount preserving full precision.

Example: '1000.500000' |
| AMOUNT | FLOAT | Decimal-adjusted token amount for human-readable values.

Example: 1000.50 |
| AMOUNT_USD | FLOAT | USD value of the token transfer at transaction time.

Example: 1000.50 |
| ORIGIN_FUNCTION_SIGNATURE | TEXT | Function signature (first 4 bytes) of the called method.

**Format**: 0x + 8 hex characters

**Common Signatures**:
- 0xa9059cbb: transfer(address,uint256)
- 0x095ea7b3: approve(address,uint256)
- 0x23b872dd: transferFrom(address,address,uint256)

**Note**: NULL for simple transfers or invalid calls |
| ORIGIN_FROM_ADDRESS | TEXT | The externally-owned account (EOA) or contract address that initiated the transaction.

**Key Points**:
- Always 42 characters (0x + 40 hex chars)
- Lowercase normalized in all tables
- Cannot be NULL for valid transactions
- For contract creation: sender of creation transaction

**Common Patterns**:
- EOA → EOA: Simple transfer
- EOA → Contract: User interaction
- Contract → Contract: Internal calls (see fact_traces)
- Known addresses: Exchange hot wallets, protocol deployers

**Query Examples**:
```sql
-- 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;
``` |