brian-terraCopy of Hellcats - Total Sales Volume - OnePlanet
    Updated 2023-01-12
    WITH transfers as (
    select distinct block_timestamp,
    tx_hash,
    event_inputs:from::string as sender,
    event_inputs:to::string as receiver,
    event_inputs:tokenId::float as tokenid
    from polygon.core.fact_event_logs
    where event_name = 'Transfer'
    and contract_address = '0x09421f533497331e1075fdca2a16e9ce3f52312b' --hellcats contract
    and tx_status = 'SUCCESS'
    )
    , nft_transfers AS (
    SELECT
    tokenid,
    receiver,
    block_timestamp,
    ROW_NUMBER() OVER (PARTITION BY tokenid ORDER BY block_timestamp DESC) AS rn
    FROM transfers
    )
    ,
    latest_transfers as (SELECT tokenid, receiver FROM nft_transfers WHERE rn = 1)


    SELECT
    receiver,
    --CONCAT(SUBSTRING(receiver, 1, 4), '...', SUBSTRING(receiver, -4)) as trunc_address,
    COUNT(DISTINCT tokenid) as nft_count
    FROM
    latest_transfers
    WHERE
    receiver
    GROUP BY
    receiver
    ORDER BY
    nft_count DESC

    Run a query to Download Data