Sandeshsharp-red
    Updated 2024-12-02
    /*

    The main goal is to classify transactions involving a specific NFT contract, focusing on identifying users who have exited their holdings or partially sold their assets.
    */

    WITH date_range AS (
    -- Extract a list of distinct transaction dates within the specified range
    SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date
    FROM ethereum.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= '2021-12-12 00:00:00.000'
    AND BLOCK_TIMESTAMP <= CURRENT_DATE
    GROUP BY 1
    ),

    nft_transactions AS (
    -- Capture buy transactions, assigning a positive transaction amount
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date,
    tx_hash,
    block_timestamp,
    block_number,
    nft_to_address AS wallet_address,
    1 AS transaction_amount
    FROM ethereum.nft.ez_nft_transfers
    WHERE block_timestamp >= '2021-12-12 00:00:00.000'
    AND nft_address = LOWER('0x49cf6f5d44e70224e2e23fdcdd2c053f30ada28b')
    UNION ALL
    -- Capture sell transactions, assigning a negative transaction amount
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS transaction_date,
    tx_hash,
    block_timestamp,
    block_number,
    nft_from_address AS wallet_address,
    QueryRunArchived: QueryRun has been archived