select date_trunc(hour,TIMESTAMP) as date, TOKEN_CONTRACT, avg(PRICE_USD) as PRICE_USD

from flow.core.fact_prices

group by 1,2

),

race_sales as (

select ifnull(PRICE_USD*PRICE, PRICE) as PRICE_USD,a.*

from flow.core.ez_nft_sales a

left join prices b

on TOKEN_CONTRACT=CURRENCY and date=date_trunc(hour,BLOCK_TIMESTAMP)

where NFT_COLLECTION='A.329feb3ab062d289.RaceDay_NFT' and TX_SUCCEEDED=TRUE)

select *,

row_number() over (order by volume desc) as rank

from

(select seller, count(*) as sales, count(distinct buyer) as buyers,

sum(PRICE_USD) as volume, avg(PRICE_USD) avg_price

from race_sales

where NFT_COLLECTION='A.329feb3ab062d289.RaceDay_NFT' and TX_SUCCEEDED=TRUE

group by seller

order by volume desc

limit 10)