with sol_price_tab AS ( SELECT RECORDED_HOUR::date AS date , avg(CLOSE) AS sol_price FROM solana.price.fact_token_prices_hourly WHERE SYMBOL = 'SOL' GROUP BY 1 ), --------------------------------------------------------------------------------------------------------------------- -- Cr this Part : Shout Out To pinehearst For Amazing Dash! NFT Wash Trading on Solana -- https://flipsidecrypto.xyz/pinehearst/nft-wash-trading-on-solana-nft-wash-trading-on-solana-CtVdWl -- Thanks <3 --------------------------------------------------------------------------------------------------------------------- -- forked from pinehearst / Solana NFT - 1. Wash vs Normal @ https://flipsidecrypto.xyz/pinehearst/q/Rf0yhSQwWVib/solana-nft-1-wash-vs-normal marketplace_label AS ( -- Get Labels for NFT Transfers SELECT program_id as program_id_label, marketplace as marketplace_label, count(distinct tx_id) as tx_Count FROM solana.nft.fact_nft_sales WHERE SUCCEEDED = TRUE AND block_id >= 151881904 AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') GROUP BY 1, 2 ), nft_sol_transfers AS ( -- Filter out Hadeswap Transfers -- spl and Sol SELECT * FROM solana.core.fact_transfers WHERE tx_id IN (SELECT distinct tx_id FROM solana.nft.fact_nft_sales WHERE 1 = 1 AND SUCCEEDED = TRUE AND program_id = 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu' AND block_id >= 151881904 ) -- First block for Hadeswap Transactions AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') ), NFT_TRANSFERS AS ( -- Find NFT Transfers for Hadeswap Transactions SELECT tx_id as tx_id0, left (index, 1) as index0, -- Indexing will be important for multiple NFT transfer tx_from as tx_from0, -- seller / giving the MINT tx_to as tx_to0, -- buyer / receiving the MINT amount as amount0, -- Default = 1 for NFTs mint -- NFT address FROM nft_sol_transfers WHERE 1 = 1 AND amount = 1 -- NFT amount should always be 1 AND mint != 'So11111111111111111111111111111111111111112' -- NFT transfers, Non-SOL transfers, by default should be NFT AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') ), SOL_TRANSFERS AS ( -- Find SOL Transfers for Hadeswap Transactions SELECT block_timestamp, block_id, tx_id, left (index, 1) as index1, sum(amount) as sol_amount FROM nft_sol_transfers WHERE mint = 'So11111111111111111111111111111111111111112' AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') GROUP BY 1, 2, 3, 4 ), --BUILD YOUR OWN FACT_NFT_SALES SOLANA_NFT_SALES AS ( -- LEFT JOIN NFT TRANSFERS TO SOL TRANSFERS, THEN UNION TO MAKE A FUNCTIONAL SOLANA NFT TABLE SELECT 'hadeswap' as marketplace, block_timestamp, block_id, tx_id, TRUE as succeeded, 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu' as program_id, -- index1 as index, -- This will be important for multiple NFT sales tx_to0 as purchaser, tx_from0 as seller, mint, sol_amount as sales_amount FROM SOL_TRANSFERS LEFT JOIN NFT_TRANSFERS ON tx_id = tx_id0 AND index0 = index1 UNION -- UNION with NON-Hadeswap NFT Sales SELECT * FROM solana.nft.fact_nft_sales WHERE program_id != 'hadeK9DLv9eA7ya5KCTqSvSvRZeJC3JgD5a9Y3CNbvu' ), pos_wash AS ( -- buyer and selling buying NFT from each other -- on the same day of sales i.e. A and B wash trading between each other -- solana doesn't have contract_address and tokenid, need to figure out how to match collections SELECT a.block_timestamp as old_timestamp, b.block_timestamp as new_timestamp, a.purchaser as buyerA, b.seller as SellerA, a.seller as SellerB, b.purchaser AS buyerB, a.tx_id as tx_wash FROM solana_nft_sales a LEFT JOIN solana_nft_sales b ON a.purchaser = b.seller AND a.seller = b.purchaser AND a.mint = b.mint -- selling the same NFT WHERE 1 = 1 AND a.succeeded = TRUE AND b.succeeded = TRUE AND a.block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND a.block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND b.block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND b.block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') ), tx_wash AS ( SELECT distinct tx_wash FROM pos_wash WHERE new_timestamp IS NOT NULL ), amm_wash AS ( -- When buyerA buys an NFT an NFT, transfer it back to Seller B and buys it again SELECT a.block_timestamp as old_timestamp, b.block_timestamp as new_timestamp, a.purchaser as buyerA, b.seller as SellerA, a.seller as SellerB, b.purchaser AS buyerB, b.tx_id as tx_wash_amm FROM solana_nft_sales a LEFT JOIN solana_nft_sales b ON a.purchaser = b.purchaser AND a.seller = b.seller AND a.mint = b.mint -- selling the same NFT AND a.block_timestamp < b.block_timestamp WHERE 1 = 1 AND a.succeeded = TRUE AND b.succeeded = TRUE AND a.block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND a.block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND b.block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND b.block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') ), tx_wash_amm AS ( SELECT distinct tx_wash_amm FROM amm_wash WHERE new_timestamp IS NOT NULL ), wash_by_mints AS ( SELECT mint as mint_washed, count(distinct tx_id) as sales_count, count(distinct purchaser) as buyers, count(distinct seller) as sellers, sum(sales_amount) FROM solana_nft_sales WHERE 1 = 1 AND succeeded = TRUE AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') GROUP BY 1 ), wash_txs_tab AS ( SELECT tx_id FROM solana_nft_sales a LEFT JOIN (SELECT tx_wash FROM tx_wash) b ON a.tx_id = tx_wash -- was trading filtered LEFT JOIN (SELECT tx_wash_amm FROM tx_wash_amm) c ON a.tx_id = tx_wash_amm -- was trading filtered LEFT JOIN (SELECT mint_washed FROM wash_by_mints WHERE sellers = 1 AND buyers = 1 AND sales_count >= 2 ) ON mint = mint_washed -- was trading filtered LEFT JOIN marketplace_label ON program_id = program_id_label WHERE 1 = 1 AND coalesce(tx_wash, tx_wash_amm, mint_washed) is not null AND succeeded = 'TRUE' AND sales_amount > 0 ), --------------------------------------------------------------------------------------------------------------------- -- Cr this Part : Shout Out To pinehearst For Amazing Dash! NFT Wash Trading on Solana -- https://flipsidecrypto.xyz/pinehearst/nft-wash-trading-on-solana-nft-wash-trading-on-solana-CtVdWl -- Thanks <3 --------------------------------------------------------------------------------------------------------------------- purchaser_tab AS ( SELECT block_timestamp , tx_id , purchaser AS trader , mint , iff( marketplace ilike 'Magic%' , 'Magic Eden' ,marketplace) AS marketplace , sales_amount * sol_price AS buy_price --,project_name||'/#'||token_id AS item FROM solana.nft.fact_nft_sales a JOIN sol_price_tab b on a.block_timestamp::date = b.date --LEFT JOIN label_tab b --on a.mint = b.mint_label OR a.mint = b.contract_address WHERE succeeded = TRUE AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND tx_id NOT IN (SELECT tx_id FROM wash_txs_tab ) ORDER BY block_timestamp ), sellers_tab AS ( SELECT block_timestamp , tx_id , seller AS trader , mint , iff( marketplace ilike 'Magic%' , 'Magic Eden' ,marketplace) AS marketplace , sales_amount * sol_price AS sales_price --, project_name||'/#'||token_id AS item FROM solana.nft.fact_nft_sales a JOIN sol_price_tab b on a.block_timestamp::date = b.date --LEFT JOIN label_tab b --on a.mint = b.mint_label OR a.mint = b.contract_address WHERE succeeded = TRUE AND block_timestamp::date >= TO_DATE('{{From_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND block_timestamp::date < TO_DATE('{{To_date_MM-DD-YYYY}}', 'MM-DD-YYYY') AND tx_id NOT IN (SELECT tx_id FROM wash_txs_tab ) ORDER BY block_timestamp ), trades_tab AS ( SELECT a.mint --, a.project_name , buy_price , a.block_timestamp AS buy_date , a.tx_id AS buy_tx , sales_price , b.block_timestamp AS sale_date , b.tx_id AS sell_tx , sales_price - buy_price AS "PnL In $USD" , a.trader , iff(a.marketplace = b.marketplace , concat ('Trade On ', initcap(a.marketplace)) , concat('Buy In ' , initcap(a.marketplace) , ' Sale In ' ,initcap(b.marketplace))) AS market ,CASE when datediff('minute',buy_date , sale_date) = 0 then '< 1/Min' when datediff('minute',buy_date , sale_date) BETWEEN 1 AND 60 then concat(datediff('minute',buy_date ,sale_date) ,'/Min') when datediff('hour',buy_date , sale_date) BETWEEN 1 AND 24 then concat(datediff('hour',buy_date ,sale_date) ,'/Hour') when datediff('day',buy_date , sale_date) >= 1 then concat(datediff('day',buy_date ,sale_date) ,'/Day') end AS Held_Time , datediff('second',buy_date , sale_date) AS Held_Time_sec FROM purchaser_tab a JOIN sellers_tab b on a.trader = b.trader AND a.mint = b.mint WHERE buy_date < sale_date --AND a.trader = 'AnrL8RwaYBEeZGnSe2dihHUJShuXzfnEXi8UzxAiGyYB' ), trades_tab_main AS ( SELECT trader , market , count(DISTINCT sell_tx) AS tx_count_m , concat('%',round((count(DISTINCT sell_tx)/sum(tx_count_m)over(partition BY trader))*100,2),' Of Txs Is: ',market) AS totall_tx_of_m , row_number()over(partition BY trader ORDER BY tx_count_m DESC) AS rn FROM trades_tab GROUP BY 1,2 qualify rn = 1 ORDER BY 1 ), final AS ( SELECT a.trader AS "Trader" , totall_tx_of_m AS "Common Platforms Usage" , count(*) AS "Trades Count (Buy Then Sell)" , round(sum("PnL In $USD"),2) AS "Realized PnL $USD" , iff(round(avg("PnL In $USD"),2) > 0 ,concat((round(avg("PnL In $USD"),2)) ,' 🟢') ,concat((round(avg("PnL In $USD"),2)) ,' 🔴')) AS "Average PnL In $USD" , round(median("PnL In $USD"),2) AS "Median PnL In $USD" , round((sum(CASE when "PnL In $USD" > 0 then 1 else 0 end)/count(*))* 100 ,2) AS "Overall Win Rate %" , CASE when avg(Held_Time_sec) < 60 then '1 min' when avg(Held_Time_sec) BETWEEN 60 AND 3600 then concat((round((avg(Held_Time_sec)/60))) ,' min') when avg(Held_Time_sec) BETWEEN 3600 AND 86400 then concat((round((avg(Held_Time_sec)/(60*60)))) ,' hour') when avg(Held_Time_sec) > 86400 then concat((round((avg(Held_Time_sec)/(60*60*24)))) ,' day') end AS "Average Time Between Buying And Selling" , row_number()over(ORDER BY "Trades Count (Buy Then Sell)" DESC) AS rn FROM trades_tab a JOIN trades_tab_main b using(trader) --WHERE trader <> 'AowKqCD2zp8Lq5ouXrVCqNgEwcwwwRSXguRAAtLS4HCC' -- Have unormal txs ... --AND buy_date::date >= current_date - interval '2 month' GROUP BY 1,2 ORDER BY 4 DESC --LIMIT 100 ), final2 AS ( SELECT p.trader , nvl(count(DISTINCT p.tx_id),0) AS "🟩Purchase Count" , nvl(count(DISTINCT s.tx_id),0) AS "🟥Sales Count" FROM sellers_tab s LEFT JOIN purchaser_tab p on p.trader = s.trader GROUP BY 1 ) SELECT "Trader" ,"Trades Count (Buy Then Sell)" ,"🟩Purchase Count" ,"🟥Sales Count" ,"Realized PnL $USD" ,"Average PnL In $USD" ,"Median PnL In $USD" ,"Overall Win Rate %" ,"Average Time Between Buying And Selling" ,"Common Platforms Usage" FROM final a JOIN final2 b on a."Trader" = b.trader --WHERE "Trades Count (Buy Then Sell)" > 50 ORDER BY 2 DESC LIMIT 10000

Solana Top Traders Tracker