SELECT

HOUR,

avg(price) as avax_price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'WAVAX'

GROUP by 1

)

, royaltyTb as (

select

block_timestamp,

TX_HASH,

DECODED_LOG:amount/1e18 as amount,

(DECODED_LOG:amount/1e18)*avax_price as amount_usd,

DECODED_LOG:royaltyRecipient as royalty_Recipient,

DECODED_LOG:collection as nft_collection,

DECODED_LOG:tokenId as token_id

from avalanche.core.fact_decoded_event_logs JOIN priceTb on date_trunc('hour', block_timestamp) = HOUR

where contract_address = '0xae079eda901f7727d0715aff8f82ba8295719977'

and event_name in ('RoyaltyPayment')

)

select

royalty_Recipient,

left(royalty_Recipient, 4) || '...' || right(royalty_Recipient, 4) as wallet,

-- coalesce(PROJECT_NAME, nft_collection) as nft,

-- CASE

-- WHEN nft_collection = '0x4c5a8b71330d751bf995472f3ab8ceb06a98dd47' THEN 'PEON'

-- WHEN nft_collection = '0xc70df87e1d98f6a531c8e324c9bcec6fc82b5e8d' THEN 'SMOL'

-- WHEN nft_collection = '0xbc3323468319cf1a2a9ca71a6f4034b7cb5f8126' THEN 'WOLFI'

-- WHEN nft_collection = '0x77e0459eb2fefa3c772a7fabe45c746377b1ed10' THEN 'LAND'

-- WHEN nft_collection = '0x77e0459eb2fefa3c772a7fabe45c746377b1ed10' THEN 'Gabe Weis'

-- WHEN nft_collection = '0xb842344669579ecf4cee12f740520376c4cbc6d1' THEN 'Gabe Weis2'

-- WHEN nft_collection = '0x357928b721890ed007142e45502a323827caf812' THEN 'CHAD'

-- WHEN nft_collection = '0x2cd4dbcbfc005f8096c22579585fb91097d8d259' THEN 'CREEPS'

-- WHEN nft_collection = '0x942a02e5ec8fc3d066da8f97a9553736369494a1' THEN 'CD Supers'

-- ELSE PROJECT_NAME end as collection,