select t1.block_timestamp as time,

t1.purchaser as WashTrader1,

t1.seller as WashTrader2,

t1.tx_id as WashTX

from solana.core.fact_nft_sales t1 left join solana.core.fact_nft_sales t2 on t1.mint = t2.mint and t1.purchaser = t2.seller

where t1.succeeded = 'TRUE' and t2.succeeded = 'TRUE')),

b as (select distinct WashTX from (

select t2.tx_id as WashTX

from solana.core.fact_nft_sales t1 left join solana.core.fact_nft_sales t2 on t1.mint = t2.mint and t1.block_timestamp < t2.block_timestamp and t1.purchaser = t2.purchaser and t1.seller = t2.seller

where t1.succeeded = 'TRUE' and t2.succeeded = 'TRUE'))

select * from (select *, row_number() over (order by Sales_Count desc) as rn

from (select purchaser,

case when (t2.WashTX is not null or (t3.WashTX is not null)) then 'Wash Trader'

when (concat(purchaser,seller) in (select concat(WashTrader4,WashTrader3) from (select tx_from as WashTrader3,

tx_to as WashTrader4

from solana.core.fact_transfers

where tx_id not in (select distinct tx_id

from solana.core.fact_nft_sales

)

)

)

)

then 'swap Back Wash Trader'

else 'legit Trader' end as type,

count (distinct tx_id) as Sales,

sum (sales_amount) as Volume

from solana.core.fact_nft_sales t1 left join a t2 on t1.tx_id = t2.WashTX

left join b t3 on t1.tx_id = t3.WashTX

where marketplace = 'hadeswap'

and succeeded = 'TRUE'

group by 1,2

)

)