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),

race_mints as (

select a.BLOCK_TIMESTAMP, a.EVENT_DATA:id as token_id, b.EVENT_DATA:to as minter

from flow.core.fact_events a

join flow.core.fact_events b

on a.TX_ID=b.TX_ID

where a.EVENT_CONTRACT='A.329feb3ab062d289.RaceDay_NFT' and a.TX_SUCCEEDED=TRUE and a.EVENT_TYPE='Minted' and b.EVENT_TYPE='Deposit' and a.EVENT_DATA:id=b.EVENT_DATA:id

),

race_fans as (

select BUYER as user, min(BLOCK_TIMESTAMP) as min_date

from race_sales

group by BUYER

union all

select SELLER, min(BLOCK_TIMESTAMP) as min_date

from race_sales

group by SELLER

union all

select minter, min(BLOCK_TIMESTAMP) as min_date

from race_mints

group by minter),

race_buyers_purchases as (

select BLOCK_TIMESTAMP, substr(NFT_COLLECTION,20) as NFT_COLLECTION, ifnull(PRICE_USD*PRICE, PRICE) as PRICE_USD, CURRENCY, BUYER