SELECT PURCHASER AS trader, MINT AS NFT, Count(*) AS sell_counts

FROM solana.core.fact_nft_sales

WHERE MINT IS NOT NULL

AND SUCCEEDED = 'TRUE'

GROUP By 1,2

HAVING sell_counts > 1

),

t2 AS ( -- TX ID of Wash Trade

SELECT TX_ID AS TWTX

FROM solana.core.fact_nft_sales

JOIN t1 ON t1.trader = solana.core.fact_nft_sales.PURCHASER AND t1.NFT = solana.core.fact_nft_sales.MINT

UNION ALL

SELECT TX_ID

FROM solana.core.fact_nft_sales

JOIN t1 ON t1.trader = solana.core.fact_nft_sales.SELLER AND t1.NFT = solana.core.fact_nft_sales.MINT

),

t3 AS(

SELECT

Date_trunc('day',BLOCK_TIMESTAMP) AS Date,

PURCHASER,

MINT,

SALES_AMOUNT,

Marketplace,

IFF(TX_ID IN (SELECT TWTX FROM t2),'Wash Trade','Normal Trade') AS Trade_status

FROM solana.core.fact_nft_sales

WHERE SUCCEEDED = 'TRUE'

AND MARKETPLACE IN ('hyperspace', 'coral cube')

SELECT Date, Marketplace,Trade_status, count(*),SUM(SALES_AMOUNT) FROM t3

WHERE Marketplace = 'hyperspace'

GROUP BY 1,2,3