with latest_transfers as (

SELECT

nft_to_address AS holder_address

FROM ethereum.nft.ez_nft_transfers

WHERE 1=1

AND block_number >= '19524912' -- Genesis block for Gam3rs NFT

AND nft_address = LOWER('0xde76ad8998310dd4c6ca9fdb03a5f20bbf01ce96') -- Gam3rs NFT contract

-- Get current holder for each token using window function

QUALIFY ROW_NUMBER() OVER (PARTITION BY tokenId ORDER BY block_number DESC) = 1

)

select holder_address from latest_transfers

group by holder_address

),

token_prices AS (

SELECT

symbol,

max_by(price, hour) AS current_price -- Latest price for each token

FROM crosschain.price.ez_prices_hourly

WHERE symbol IN ('ETH', 'POL', 'AVAX')

AND token_address IS NULL -- Ensures we're getting native token prices

GROUP BY symbol

),

reservoir_api_data AS (

-- Ethereum data

SELECT

'Ethereum' AS blockchain,

holder_address,

live.udf_api('GET',concat('https://api.reservoir.tools/users/', holder_address, '/collections/v3?excludeSpam=true'),{'Authorization': 'Bearer ' || '{Reservoir}'}, null):"data":"collections" AS api_response

FROM gam3rs_members

WHERE holder_address != '0x0000000000000000000000000000000000000000'