SELECT

DISTINCT buyer

FROM flow.core.ez_nft_sales

WHERE NFT_COLLECTION LIKE '%RaceDay%'

),

lists as (

select

distinct buyer as fan

from flow.core.ez_nft_sales

where nft_collection LIKE '%RaceDay%' and TX_SUCCEEDED = 'TRUE'

),

top1collections as(

select

split_part(nft_collection, '.', -1) as collection,

count(*) as number_of_buy

from flow.core.ez_nft_sales inner join lists on fan=buyer

GROUP BY 1

ORDER BY 2 DESC

LIMIT 10

)

SELECT

date_trunc('day', block_timestamp) as day,

split_part(nft_collection, '.', -1) as NFTCOLLECTION,

sum(price) as volume,

avg(price) as avg_price,

median(price) as median_price,

count(DISTINCT tx_id) as sales_events

FROM flow.core.ez_nft_sales

WHERE buyer in (SELECT * FROM tab1) and NFTCOLLECTION in (select collection from top1collections)

GROUP BY 1, 2

order by 1