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;