Ez Nft Transfers

SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS total_transfers,
    COUNT(DISTINCT contract_address) AS unique_collections,
    COUNT(DISTINCT CASE WHEN is_mint THEN tx_hash END) AS mint_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(IFF(token_standard = 'erc721', 1 , 0)) as erc721_transfer_count,
    SUM(IFF(token_standard = 'erc1155', 1 , 0)) as erc1155_transfer_count
FROM <blockchain_name>.nft.ez_nft_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 1 DESC;

SELECT
    contract_address,
    name,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT token_id) AS unique_tokens,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(CASE WHEN is_mint THEN 1 ELSE 0 END) AS mints,
    SUM(CASE WHEN to_address = '0x0000000000000000000000000000000000000000' THEN 1 ELSE 0 END) AS burns
FROM <blockchain_name>.nft.ez_nft_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND name IS NOT NULL
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 50;

SELECT
    contract_address,
    name,
    DATE_TRUNC('hour', block_timestamp) AS mint_hour,
    COUNT(*) AS mint_count,
    COUNT(DISTINCT to_address) AS unique_minters,
    COUNT(DISTINCT token_id) as unique_token_id_count
FROM <blockchain_name>.nft.ez_nft_transfers
WHERE is_mint = TRUE
    AND block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

WITH wallet_activity AS (
    SELECT
        address,
        SUM(received) AS nfts_received,
        SUM(sent) AS nfts_sent,
        SUM(received) - SUM(sent) AS net_change,
        COUNT(DISTINCT contract_address) AS collections_interacted
    FROM (
        SELECT to_address AS address, COUNT(*) AS received, 0 AS sent, contract_address
        FROM <blockchain_name>.nft.ez_nft_transfers
        WHERE block_timestamp >= CURRENT_DATE - 30
            AND to_address != '0x0000000000000000000000000000000000000000'
        GROUP BY 1, 4

        UNION ALL

        SELECT from_address AS address, 0 AS received, COUNT(*) AS sent, contract_address
        FROM <blockchain_name>.nft.ez_nft_transfers
        WHERE block_timestamp >= CURRENT_DATE - 30
            AND from_address != '0x0000000000000000000000000000000000000000'
        GROUP BY 1, 4
    )
    GROUP BY 1
)
SELECT * FROM wallet_activity
WHERE collections_interacted > 5
ORDER BY net_change DESC
LIMIT 100;

SELECT
    contract_address,
    name,
    token_transfer_type,
    COUNT(*) AS transfer_count
FROM <blockchain_name>.nft.ez_nft_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND token_transfer_type in (
        'erc1155_TransferSingle',
        'erc1155_TransferBatch'
        )
GROUP BY 1, 2, 3
ORDER BY 4 DESC;

SELECT
    to_address,
    contract_address,
    token_id
FROM <blockchain_name>.nft.ez_nft_transfers
WHERE contract_address = '0xbd3531da5cf5857e7cfaa92426877b022e612cf8'
QUALIFY ROW_NUMBER() OVER (PARTITION BY contract_address, token_id ORDER BY block_number DESC, event_index DESC) =1;

-- 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;
``` |
| INTRA_EVENT_INDEX | NUMBER | Position within a batch transfer event, primarily for ERC-1155. Always starts with 1 for single transfers.

Example: 1 |
| TOKEN_TRANSFER_TYPE | TEXT | The specific event type emitted by the contract. Values include 'erc721_Transfer', 'erc1155_TransferSingle', 'erc1155_TransferBatch', etc.

Example: 'erc721_Transfer' |
| IS_MINT | BOOLEAN | Boolean flag indicating if this transfer is a minting event (from address is 0x0).

Example: true |
| FROM_ADDRESS | TEXT | The address sending/transferring the NFT. Special value of '0x0000000000000000000000000000000000000000' indicates minting event.

Example: '0x1234567890123456789012345678901234567890' |
| TO_ADDRESS | TEXT | The address receiving the NFT. Special value of '0x0000000000000000000000000000000000000000' indicates burning event.

Example: '0x1234567890123456789012345678901234567890' |
| CONTRACT_ADDRESS | TEXT | The address of the contract that emitted the NFT transfer event.

Example: '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d' |
| TOKEN_ID | TEXT | The unique identifier for a specific NFT within a collection. String format to handle various token_id formats.

Example: '1234' |
| QUANTITY | TEXT | The number of NFTs transferred for this specific token_id. Always 1 for ERC-721, can be more for ERC-1155.

Example: 1 |
| TOKEN_STANDARD | TEXT | The standard of the NFT. Values include 'erc721', 'erc1155', 'cryptopunks', and 'legacy'.

Example: 'erc721' |
| NAME | TEXT | The name of the NFT collection. For Ethereum only, join with nft.dim_nft_collection_metadata for token-level details.

Example: 'Bored Ape Yacht Club' |
| 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;
``` |