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 wash_Volume desc) as rn

from (

select seller,

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

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 Trading'

else 'legit Trading' end as type,

sum (case when nvl (t2.WashTX,t3.WashTX) is not null then sales_amount else 0 end) as wash_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 type in ('Wash Trading','swap Back Wash Trading')

and succeeded = 'TRUE'

group by 1,2)) where rn <= 10