Ez Nft Sales

-- Daily NFT market volume by platform
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform_name,
    COUNT(*) as sales_count,
    COUNT(DISTINCT tx_hash) AS unique_sales_transaction_count,
    COUNT(DISTINCT buyer_address) AS unique_buyers,
    COUNT(DISTINCT contract_address) AS collections_traded,
    SUM(price_usd) AS total_volume_usd,
    AVG(price_usd) AS avg_sale_price_usd
FROM <blockchain_name>.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 30
    AND price_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 7 DESC;

-- Top selling NFT collections
SELECT
    contract_address,
    name AS collection_name,
    COUNT(*) AS sales_count,
    COUNT(DISTINCT token_id) AS unique_tokens_sold,
    COUNT(DISTINCT buyer_address) AS unique_buyers,
    SUM(price_usd) AS total_volume_usd,
    AVG(price_usd) AS avg_price_usd,
    MAX(price_usd) AS highest_sale_usd
FROM <blockchain_name>.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 7
    AND price_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 6 DESC
LIMIT 50;

-- Marketplace competition analysis
WITH platform_metrics AS (
    SELECT
        platform_name,
        COUNT(*) AS total_sales,
        SUM(price_usd) AS total_volume_usd,
        COUNT(DISTINCT buyer_address) AS unique_buyers,
        COUNT(DISTINCT seller_address) AS unique_sellers,
        AVG(platform_fee / NULLIF(price, 0) * 100) AS avg_platform_fee_pct,
        SUM(platform_fee_usd) AS total_platform_revenue_usd
    FROM <blockchain_name>.nft.ez_nft_sales
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND price_usd IS NOT NULL
    GROUP BY 1
)
SELECT
    platform_name,
    total_sales,
    total_volume_usd,
    total_volume_usd * 100.0 / SUM(total_volume_usd) OVER () AS market_share_pct,
    unique_buyers,
    unique_sellers,
    avg_platform_fee_pct,
    total_platform_revenue_usd
FROM platform_metrics
ORDER BY total_volume_usd DESC;

-- Whale activity tracking
WITH buyer_stats AS (
    SELECT
        buyer_address,
        COUNT(*) AS purchases,
        COUNT(DISTINCT contract_address) AS unique_collections,
        SUM(price_usd) AS total_spent_usd,
        AVG(price_usd) AS avg_purchase_price,
        MAX(price_usd) AS highest_purchase
    FROM <blockchain_name>.nft.ez_nft_sales
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND price_usd IS NOT NULL
    GROUP BY 1
)
SELECT
    CASE
        WHEN total_spent_usd < 1000 THEN '< $1K'
        WHEN total_spent_usd < 10000 THEN '$1K - $10K'
        WHEN total_spent_usd < 100000 THEN '$10K - $100K'
        WHEN total_spent_usd < 1000000 THEN '$100K - $1M'
        ELSE '> $1M'
    END AS buyer_tier,
    COUNT(*) AS buyer_count,
    SUM(purchases) AS total_purchases,
    AVG(unique_collections) AS avg_collections_per_buyer,
    SUM(total_spent_usd) AS tier_total_spent
FROM buyer_stats
GROUP BY 1
ORDER BY MIN(total_spent_usd);

-- Creator royalty analysis
SELECT
    name AS collection_name,
    contract_address,
    COUNT(*) AS sales_with_royalties,
    SUM(creator_fee) AS total_creator_fees,
    SUM(creator_fee_usd) AS total_creator_fees_usd,
    AVG(creator_fee / NULLIF(price, 0) * 100) AS avg_royalty_pct,
    SUM(creator_fee_usd) / NULLIF(SUM(price_usd), 0) * 100 AS effective_royalty_rate
FROM <blockchain_name>.nft.ez_nft_sales
WHERE creator_fee > 0
    AND price > 0
    AND block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
HAVING COUNT(*) > 10
ORDER BY total_creator_fees_usd DESC
LIMIT 100;

-- 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;
``` |
| EVENT_TYPE | TEXT | The specific type of NFT transaction that occurred. Common values include 'sale', 'bid_won', 'redeem', and 'mint'.

Example: 'sale' |
| PLATFORM_ADDRESS | TEXT | The smart contract address of the marketplace facilitating the sale. Platforms may have multiple contracts for different versions.

Example: '0x00000000006c3852cbef3e08e8df289169ede581' |
| PLATFORM_NAME | TEXT | The marketplace or platform where the NFT sale occurred. Standardized to lowercase with no spaces.

Example: 'opensea' |
| PLATFORM_EXCHANGE_VERSION | TEXT | The version identifier of the marketplace contract. Tracks protocol versions and upgrades.

Example: 'seaport_1_5' |
| AGGREGATOR_NAME | TEXT | The NFT aggregator platform that routed the transaction. NULL indicates direct platform interaction.

Example: 'gem' |
| SELLER_ADDRESS | TEXT | The blockchain address that sold the NFT.

Example: '0x1234567890123456789012345678901234567890' |
| BUYER_ADDRESS | TEXT | The blockchain address that purchased the NFT.

Example: '0x1234567890123456789012345678901234567890' |
| CONTRACT_ADDRESS | TEXT | The smart contract address of the NFT collection. Supports ERC-721, ERC-1155, and custom implementations.

Example: '0xbc4ca0eda7647a8ab7c2061c2e118a18a936f13d' |
| NAME | TEXT | The name of the NFT collection or project. May be NULL for unverified collections.

Example: 'Bored Ape Yacht Club' |
| TOKEN_ID | TEXT | The unique identifier of the specific NFT within its collection. Format is numeric string.

Example: '1234' |
| QUANTITY | TEXT | The number of tokens sold in the transaction. Primarily relevant for ERC-1155 tokens.

Example: '1' |
| TOKEN_STANDARD | TEXT | The technical standard implemented by the NFT contract. Common values include 'erc721', 'erc1155', 'cryptopunks', and 'legacy'.

Example: 'erc721' |
| CURRENCY_SYMBOL | TEXT | The symbol of the token used for payment.

Example: 'ETH' |
| CURRENCY_ADDRESS | TEXT | The contract address of the payment token. Shows 'ETH' for native ETH payments.

Example: '0xa0b86a33e6776a1e7f9f0b8b8b8b8b8b8b8b8b8b' |
| PRICE | FLOAT | The sale price in the payment currency, including platform and creator fees. Raw price before USD conversion.

Example: 2.5 |
| PRICE_USD | FLOAT | The sale price converted to USD at transaction time, including platform and creator fees. May be NULL for missing price data.

Example: 4250.75 |
| TOTAL_FEES | FLOAT | The combined platform and creator fees in the payment currency.

Example: 0.125 |
| PLATFORM_FEE | FLOAT | The fee charged by the marketplace in the payment currency. Typically 2-2.5% of sale price.

Example: 0.05 |
| CREATOR_FEE | FLOAT | The royalty fee paid to the collection creator in the payment currency. Typically 0-10% of sale price.

Example: 0.075 |
| TOTAL_FEES_USD | FLOAT | The combined platform and creator fees converted to USD.

Example: 212.54 |
| PLATFORM_FEE_USD | FLOAT | The marketplace fee converted to USD.

Example: 85.02 |
| CREATOR_FEE_USD | FLOAT | The royalty fee converted to USD.

Example: 127.52 |
| TX_FEE | FLOAT | Total fee paid for transaction execution in native token units.

Example: 0.002 |
| TX_FEE_USD | FLOAT | The transaction fee denominated in USD. Use only one instance per transaction to avoid overcounting.

Example: 45.32 |
| 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;
``` |