SELECT

PURCHASER,

Mint,

min(block_timestamp) as buying_date,

avg(sales_amount) as buying_price

FROM solana.core.fact_nft_sales

GROUP BY 1,2

), sellerTb as (

SELECT

SELLER,

s.Mint,

min(block_timestamp) as selling_date,

avg(sales_amount) as selling_price

FROM solana.core.fact_nft_sales s join buyerTb b on s.SELLER = b.PURCHASER and s.Mint = b.mint

GROUP BY 1,2

-- , topTraders as (

SELECT

PURCHASER,

LEFT(b.PURCHASER, 4) || '...' || RIGHT(b.PURCHASER, 4) as short_add,

sum(buying_price - selling_price) as profit_sol,

avg(datediff('hour', buying_date::date, selling_date::date)) as avg_hodl_time

FROM buyerTb b left outer join sellerTb s on PURCHASER = SELLER and b.mint = s.mint and selling_date > buying_date

where not b.mint is NULL

AND not seller is NULL

GROUP BY 1,2

HAVING NOT profit_sol is NULL

AND AVG_HODL_TIME > 0

ORDER BY 3 DESC

LIMIT 100