-- forked from 2023-09-04 01:11 AM @ https://flipsidecrypto.xyz/edit/queries/1bd91d98-6ffc-4368-8c61-c0bb463bde63 with p_tab AS ( SELECT hour::date AS date , avg(price) AS token_price , token_address ,decimals FROM ethereum.price.ez_hourly_token_prices GROUP BY 1 , 3 , 4 UNION SELECT hour::date AS date , avg(price) AS token_price , 'ETH' AS token_address ,'18' AS decimals FROM ethereum.price.ez_hourly_token_prices WHERE symbol = 'WETH' AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' GROUP BY 1 , 3 , 4 ), --------------------------------------------------------------------------------------------------------------------- -- Cr this Part : Shout Out To pinehearst For Amazing Dash! Wash Trading on ETH-By Sales Type -- https://flipsidecrypto.xyz/pinehearst/q/NPLLlyEMbThJ/wash-trading-on-eth-by-sales-type -- Thanks <3 --------------------------------------------------------------------------------------------------------------------- ORACLE_PRICES AS ( SELECT hour, price as eth_usd FROM ethereum.price.ez_hourly_token_prices WHERE symbol = 'WETH' AND token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ), eth_weth_transfers AS ( -- combine WETH and ETH transfers SELECT date_trunc('day', block_timestamp) as date, eth_from_address, -- A eth_to_address, -- B amount as eth FROM ethereum.core.ez_eth_transfers WHERE 1=1 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 eth_from_address IN (SELECT distinct buyer_address FROM ethereum.nft.ez_nft_sales) -- filter out users that traded NFT AND tx_hash NOT IN (SELECT distinct tx_hash FROM ethereum.nft.ez_nft_sales) -- filter out ETH transfer from buying/selling UNION SELECT date_trunc('day', block_timestamp) as date, from_address, -- A to_address, -- B amount/pow(10,18) as weth FROM ethereum.core.ez_token_transfers WHERE 1=1 AND contract_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' 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 from_address IN (SELECT distinct buyer_address FROM ethereum.nft.ez_nft_sales) -- filter out users that traded NFT AND tx_hash NOT IN (SELECT distinct tx_hash FROM ethereum.nft.ez_nft_sales) -- filter out WETH transfer from buying/selling i.e. back end WETH transfers ), eth_tx AS ( -- Find all daily transfers of ETH between NFT Buyers and Sellers outside of an NFT sales SELECT date, eth_from_address, -- A eth_to_address, -- B sum(eth) as eth_transferred -- ETH/WETH transferred for the day FROM eth_weth_transfers GROUP BY 1,2,3 ), pos_wash AS ( -- if A sell to B and B sells back to A the same NFT and ID, vice-versa SELECT a.tx_hash as tx_wash, b.tx_hash as tx_wash_pair, -- First Sale a / A sells NFT to B a.seller_address as sellerA, -- A a.buyer_address as buyerB, -- B -- Second Sale b / B sells NFT back to A b.seller_address as sellerB, -- B b.buyer_address as buyerA -- A FROM ethereum.nft.ez_nft_sales a LEFT JOIN ethereum.nft.ez_nft_sales b ON a.nft_address = b.nft_address AND a.tokenid = b.tokenid -- same NFT AND a.buyer_address = b.seller_address -- buyer was a seller of the nft AND a.seller_address = b.buyer_address -- seller was a buyer of the nft 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 a.block_timestamp > b.block_timestamp -- does it have to preceed? i.e. block_timestamp A > block_timestamp B // no, so long the NFT exchanged the same hands we should count it as a wash. ), pos_wash_tx AS ( -- Finding tx_hashes SELECT DISTINCT tx_wash as pos_wash_tx FROM pos_wash WHERE tx_wash_pair IS NOT NULL ), transfer_wash AS ( -- if A sells to B and B transfers the NFT back to A to sell SELECT a.tx_hash as tx_wash, b.tx_hash as tx_wash_pair, -- First Sale a / A sells NFT to B a.seller_address as sellerA, -- A a.buyer_address as buyerB, -- B -- Second Sale b / back end transfer of NFT from B back to A b.seller_address as sellerA, -- A b.buyer_address as buyerB -- B FROM ethereum.nft.ez_nft_sales a LEFT JOIN ethereum.nft.ez_nft_sales b ON a.nft_address = b.nft_address AND a.tokenid = b.tokenid -- same NFT AND a.buyer_address = b.buyer_address -- same buyer AND a.seller_address = b.seller_address -- same seller AND a.block_timestamp < b.block_timestamp -- next sales after the first sale 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') ), transfer_wash_tx AS ( -- filtering out transactions labelled as wash SELECT DISTINCT tx_wash AS transfer_wash_tx FROM transfer_wash WHERE tx_wash_pair IS NOT NULL ), labelled AS ( SELECT case when coalesce(pos_wash_tx, transfer_wash_tx, eth_transferred) is not null then 'Wash' else 'Normal' end as sales_type, tx_hash --, nft_address, project_name, tokenid FROM ethereum.nft.ez_nft_sales LEFT JOIN ORACLE_PRICES ON date_trunc('hour', block_timestamp) = hour -- / To find ETH denomination of NFT LEFT JOIN pos_wash_tx ON tx_hash = pos_wash_tx -- Point-of-sales Wash: Cyclical wash pattern LEFT JOIN transfer_wash_tx ON tx_hash = transfer_wash_tx -- NFT Transfer Wash: LEFT JOIN eth_tx ON buyer_address = eth_to_address -- Self-Funded Wash: buyer (B) received ETH from... AND seller_address = eth_from_address -- seller (A) AND date_trunc('day', block_timestamp) = date -- on the same day on the same day of sales WHERE 1=1 ), --------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------- --avg_collection_p_tab AS ( --SELECT -- block_timestamp::date AS date_p --, nft_address --, avg(price * token_price) AS avg_coll_p --FROM ethereum.nft.ez_nft_sales a --JOIN p_tab b --on a.block_timestamp::date = b.date AND a.currency_address = b.token_address --WHERE tx_hash NOT IN (SELECT tx_hash FROM labelled WHERE sales_type = 'Wash') --GROUP BY 1 , 2 --), market_tab_p AS ( SELECT block_timestamp , tx_hash , buyer_address AS trader , a.nft_address AS colection_address , project_name AS collection_name , tokenid , a.nft_address|| ' #'|| tokenid AS item , price * token_price AS buy_price , platform_name FROM ethereum.nft.ez_nft_sales a JOIN p_tab b on a.block_timestamp::date = b.date AND a.currency_address = b.token_address --JOIN avg_collection_p_tab c --on a.block_timestamp::date = c.date_p AND a.nft_address = c.nft_address WHERE currency_address IS NOT NULL -- for failed txs 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_hash NOT IN (SELECT tx_hash FROM labelled WHERE sales_type = 'Wash') ORDER BY block_timestamp ), market_tab_s AS ( SELECT block_timestamp , tx_hash , seller_address AS trader , a.nft_address AS colection_address , project_name AS collection_name , tokenid , a.nft_address|| ' #'|| tokenid AS item , price * token_price AS sale_price , platform_name FROM ethereum.nft.ez_nft_sales a JOIN p_tab b on a.block_timestamp::date = b.date AND a.currency_address = b.token_address --JOIN avg_collection_p_tab c --on a.block_timestamp::date = c.date_p AND a.nft_address = c.nft_address WHERE currency_address IS NOT NULL -- for failed txs 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_hash NOT IN (SELECT tx_hash FROM labelled WHERE sales_type = 'Wash') ORDER BY block_timestamp ), trades_tab AS ( SELECT a.item , a.collection_name , a.block_timestamp AS buy_date , a.tx_hash AS buy_tx , sale_price - buy_price AS "PnL In $USD" , sale_price , buy_price , b.block_timestamp AS sale_date , b.tx_hash AS sell_tx , a.trader , iff(a.platform_name = b.platform_name , concat ('Trade On ', initcap(a.platform_name)) , concat('Buy In ' , initcap(a.platform_name) , ' Sale In ' ,initcap(b.platform_name))) 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 market_tab_p a JOIN market_tab_s b on a.trader = b.trader AND a.item = b.item WHERE buy_date < sale_date AND "PnL In $USD" <= 10000 ), 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_hash),0) AS "🟩Purchase Count" , nvl(count(DISTINCT s.tx_hash),0) AS "🟥Sales Count" FROM market_tab_s s LEFT JOIN market_tab_p 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

Byte benders Trader Tracker