with sol_prices as ( select date_trunc('hour',block_timestamp) as date_hour, case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_to_mint else swap_from_mint end as token_address, sum(case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_from_amount else swap_to_amount end) as stable_amount, sum(case when swap_to_mint = 'So11111111111111111111111111111111111111112' then swap_to_amount else swap_from_amount end) as token_amount, stable_amount / token_amount as price from solana.core.fact_swaps swaps where succeeded = TRUE and block_timestamp > current_date() - interval '{{months}} months' and (swap_from_mint = 'So11111111111111111111111111111111111111112' or swap_to_mint = 'So11111111111111111111111111111111111111112') and (swap_from_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB') or swap_to_mint in ('EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v','Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')) group by 1,2 ), me_sales as ( select block_timestamp, tx_id, purchaser as user_address, 'purchase' as label_action, mint from solana.core.fact_nft_sales where marketplace like 'magic eden v%' and block_timestamp > current_date() - interval '{{months}} months' and succeeded and sales_amount > 0 union all select block_timestamp, tx_id, seller as user_address, 'sale' as label_action, mint from solana.core.fact_nft_sales where marketplace like 'magic eden v%' and block_timestamp > current_date() - interval '{{months}} months' and succeeded and sales_amount > 0 ), me_wash as ( select *, 'WASH' as label, lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) as next_tx, lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) as prev_tx, lead(block_timestamp,1) over (partition by user_address, mint order by block_timestamp) as next_timestamp, lag(block_timestamp,1) over (partition by user_address, mint order by block_timestamp) as prev_timestamp, datediff('days',prev_timestamp,block_timestamp) as diff_prev, datediff('days',block_timestamp,next_timestamp) as diff_next from me_sales qualify ((lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) is not null and lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) <> tx_id) or (lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) is not null and lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) <> tx_id) ) and (datediff('day',block_timestamp,lead(block_timestamp,1) over (partition by user_address, mint order by block_timestamp)) < 7 or datediff('day',lag(block_timestamp,1) over (partition by user_address, mint order by block_timestamp),block_timestamp) < 7 ) -- wash trades ), aggregated_me as ( select user_address, 'MagicEden' as marketplace, sum(case when label = 'WASH' then sales_amount * price end) as usd_vol, usd_vol / sum(sales_amount * price) * 100 as vol_pct from solana.core.fact_nft_sales sales left join (select distinct tx_id, label from me_wash) wash using(tx_id) left join me_sales using(tx_id) left join sol_prices on date_trunc('hour',sales.block_timestamp) = sol_prices.date_hour where marketplace like 'magic eden v%' and block_timestamp > current_date() - interval '{{months}} months' and succeeded and sales_amount > 0 group by 1,2 ), hs_pools as ( select instruction:accounts[8] as pos1, instruction:accounts[12] as pos2, inner_instruction:instructions[0]:parsed:info:newAccount as pool_address, inner_instruction:instructions[0]:parsed:info:source as pool_owner from solana.core.fact_events where program_id = 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu' and index = 0 and succeeded and inner_instruction:instructions[0]:parsed:type = 'createAccount' and inner_instruction:instructions[0]:parsed:info:owner = 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu' ), hs_sales as ( select distinct block_timestamp, tx_id, transfers.mint, case when purchaser = pos1 or purchaser = pos2 then 'sale' else 'purchase' end as label_action, case when label_action = 'sale' then tx_from else tx_to end as user_address from solana.core.fact_nft_sales sales inner join hs_pools on (sales.purchaser = pos1 or sales.seller = pos1) or (sales.purchaser = pos2 or sales.seller = pos2) inner join solana.core.fact_transfers transfers using(tx_id, block_timestamp) where marketplace = 'hadeswap' and block_timestamp > current_date() - interval '{{months}} months' and succeeded and not transfers.mint = 'So11111111111111111111111111111111111111112' ), hs_wash as ( select *, lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) as next_tx, lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) as prev_tx, lead(block_timestamp,1) over (partition by user_address, mint order by block_timestamp) as next_timestamp, lag(block_timestamp,1) over (partition by user_address, mint order by block_timestamp) as prev_timestamp, datediff('days',prev_timestamp,block_timestamp) as diff_prev, datediff('days',block_timestamp,next_timestamp) as diff_next, case when ((lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) is not null and lead(tx_id,1) over (partition by user_address, mint order by block_timestamp) <> tx_id) or (lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) is not null and lag(tx_id,1) over (partition by user_address, mint order by block_timestamp) <> tx_id) ) and (datediff('day',block_timestamp,lead(block_timestamp,1) over (partition by user_address, mint order by block_timestamp)) < {{wash_days}} or datediff('day',lag(block_timestamp,1) over (partition by user_address, mint order by block_timestamp),block_timestamp) < {{wash_days}}) then 'WASH' else 'LEGIT' end as label -- case can also be used as qualify condition from hs_sales ), tx_amounts as ( select tx_id, sum(sales_amount) as sol, sum(sales_amount * price) as usd from solana.core.fact_nft_sales sales left join sol_prices on date_trunc('hour',sales.block_timestamp) = sol_prices.date_hour where marketplace = 'hadeswap' and block_timestamp > current_date() - interval '{{months}} months' and succeeded group by 1 ), aggregated_hs as ( select user_address, 'Hadeswap' as marketplace, sum(case when label = 'WASH' then usd end) as usd_vol, usd_vol / sum(usd) * 100 as vol_pct from hs_wash left join tx_amounts using(tx_id) group by 1,2 ), binned as ( select marketplace, case when usd_vol < 10 then floor(usd_vol) when usd_vol < 100 then floor(usd_vol / 10.0)*10 when usd_vol < 1000 then floor(usd_vol / 100.0)*100 when usd_vol < 10000 then floor(usd_vol / 1000.0)*1000 when usd_vol < 100000 then floor(usd_vol / 10000.0)*10000 when usd_vol < 1000000 then floor(usd_vol / 100000.0)*100000 when usd_vol < 10000000 then floor(usd_vol / 1000000.0)*1000000 when usd_vol < 100000000 then floor(usd_vol / 10000000.0)*10000000 else usd_vol end as bin, count(distinct user_address) as users from ( select * from aggregated_me union all select * from aggregated_hs ) group by 1,2 ) select * from binned where bin > 0 order by marketplace

Solana NFT Wash Trading: Hadeswap & MagicEden