SELECT

HOUR,

avg(price) as avax_price

FROM ethereum.core.fact_hourly_token_prices

WHERE symbol = 'WAVAX'

GROUP by 1

)

,salesTb as (

select

block_timestamp,

TX_HASH,

DECODED_LOG:collection as nft_collection,

DECODED_LOG:price/1e18 as amount,

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

DECODED_LOG:maker as seller,

DECODED_LOG:taker as buyer,

DECODED_LOG:tokenId as tokenId

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

where contract_address = '0xae079eda901f7727d0715aff8f82ba8295719977'

and event_name in ('TakerBid','TakerAsk')

select

count(DISTINCT tx_hash) as sales_tx_cnt,

count(DISTINCT buyer) as buyer_cnt,

count(DISTINCT seller) as saller_cnt,

count(DISTINCT nft_collection) as collection_cnt,

count(DISTINCT tokenId) as nft_cnt,

sum(amount) as sales_vol,

sum(amount_usd) as sales_vol_usd,

avg(amount) as avg_sales_vol,

avg(amount_usd) as avg_sales_vol_usd

from salesTb