denze-e7j2NU{{nft_contract_address}} % of Purchase Volume by Holders vs. Median Price ($)
    Updated 2023-05-30
    -- forked from The Captainz: % of Purchase Volume by Holders vs. Median Price ($) @ https://flipsidecrypto.xyz/edit/queries/b2cd8094-a874-473f-8a6c-46f39c2c2815

    /*
    holder: wallet held a Captainz at the start of the day, and still holds at least 1 as of current date
    */

    WITH

    input_contracts AS (
    SELECT
    trim(F.value) AS nft_contract_address
    FROM (
    SELECT
    SPLIT(data.nft_contract_address, ';') AS input
    FROM VALUES
    (lower('{{nft_contract_address}}')) -- Captainz
    AS data(nft_contract_address)
    ) i
    , Table(Flatten(i.input)) AS F
    WHERE trim(F.value) regexp '^0x[0-9a-fA-F]{40}$' -- check address is a valid format, i.e. starts with 0x and has 42 characters total
    )

    -- get all nft transfers for the given collection(s)
    , nft_transfers AS (
    SELECT
    t.nft_address AS nft_contract_address
    , t.tokenid AS token_id
    , t.block_timestamp AS block_time
    , date_trunc('day', t.block_timestamp) AS day
    , t.nft_from_address AS from_address
    , t.nft_to_address AS to_address
    , COALESCE(erc1155_value, 1) AS amount
    , CASE WHEN erc1155_value is not null THEN 'true' ELSE 'false' END AS is_erc1155
    , ROW_NUMBER() OVER (PARTITION BY t.nft_address, t.tokenid ORDER BY block_number DESC, event_index DESC) AS rank_desc
    FROM ethereum.core.ez_nft_transfers t
    Run a query to Download Data