SELECT

TX_ID,

BLOCK_TIMESTAMP,

x.NFT_COLLECTION,

x.NFT_ID,

BUYER,

SELLER,

PRICE,

CURRENCY,

MARKETPLACE,

TX_SUCCEEDED,

player

FROM flow.nft.ez_nft_sales x

join flow.nft.dim_topshot_metadata y on x.nft_id=y.nft_id

WHERE x.NFT_COLLECTION ilike '%topshot%'

AND TX_SUCCEEDED = TRUE and player in (

select distinct player FROM (select distinct player, sum(price) as total_price FROM flow.nft.ez_nft_sales x

join flow.nft.dim_topshot_metadata y on x.nft_id=y.nft_id

WHERE x.NFT_COLLECTION ilike '%topshot%' and block_timestamp>current_date-INTERVAL '1 YEAR' group by 1 order by 2 desc limit 10

),

all_time AS (

SELECT

trunc(block_timestamp,'day') as date,

player,

COUNT(DISTINCT TX_ID) AS total_sales,

COUNT(DISTINCT BUYER) AS total_buyers,

COUNT(DISTINCT SELLER) AS total_sellers,

SUM(PRICE) AS total_sales_value,

AVG(PRICE) AS avg_sale_price,

MAX(PRICE) AS max_sale_price,

MIN(PRICE) AS min_sale_price,

COUNT(DISTINCT NFT_ID) AS total_unique_nfts_sold

--EXTRACT(YEAR FROM BLOCK_TIMESTAMP) AS year,