WITH -- CTE 1: Fetch all raw buy/sell transactions from the last 30 days. transactions AS ( SELECT BLOCK_TIMESTAMP, SWAPPER AS WALLET, SWAP_FROM_AMOUNT AS ORIGINAL_SWAP_FROM_AMOUNT, SWAP_TO_AMOUNT AS ORIGINAL_SWAP_TO_AMOUNT, SWAP_FROM_MINT AS ORIGINAL_SWAP_FROM_MINT, SWAP_TO_MINT AS ORIGINAL_SWAP_TO_MINT, CASE WHEN SWAP_FROM_MINT = 'So11111111111111111111111111111111111111112' THEN 'buy' WHEN SWAP_TO_MINT = 'So11111111111111111111111111111111111111112' THEN 'sell' ELSE 'unknown' END AS txtype, CASE WHEN txtype = 'buy' THEN SWAP_TO_MINT WHEN txtype = 'sell' THEN SWAP_FROM_MINT ELSE 'unknown' END AS TOKEN_ADDRESS, CASE WHEN txtype = 'buy' THEN -ORIGINAL_SWAP_FROM_AMOUNT WHEN txtype = 'sell' THEN ORIGINAL_SWAP_TO_AMOUNT ELSE 0 END AS AMOUNT_TRADED, CASE WHEN txtype = 'buy' THEN ORIGINAL_SWAP_TO_AMOUNT WHEN txtype = 'sell' THEN -ORIGINAL_SWAP_FROM_AMOUNT ELSE 0 END AS TOKEN_AMOUNT_TRADED, CASE WHEN txtype = 'buy' THEN ORIGINAL_SWAP_FROM_AMOUNT / ORIGINAL_SWAP_TO_AMOUNT WHEN txtype = 'sell' THEN ORIGINAL_SWAP_TO_AMOUNT / ORIGINAL_SWAP_FROM_AMOUNT ELSE NULL END AS CALCULATED_TOKEN_PRICE FROM solana.defi.ez_dex_swaps WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL ' 50 DAY' AND txtype IN ('buy', 'sell') AND ORIGINAL_SWAP_TO_AMOUNT != 0 AND ORIGINAL_SWAP_FROM_AMOUNT != 0 ), -- NEW CTE: Identify if a token for a wallet is "sell-only" wallet_token_tx_type_summary AS ( SELECT WALLET, TOKEN_ADDRESS, MAX(CASE WHEN txtype = 'buy' THEN 1 ELSE 0 END) AS has_buy_tx, MAX(CASE WHEN txtype = 'sell' THEN 1 ELSE 0 END) AS has_sell_tx FROM transactions GROUP BY WALLET, TOKEN_ADDRESS ), -- NEW CTE: Calculate the percentage of sell-only tokens for each wallet wallet_sell_only_percentage AS ( SELECT wtts.WALLET, SUM(CASE WHEN wtts.has_buy_tx = 0 AND wtts.has_sell_tx = 1 THEN 1 ELSE 0 END) AS count_sell_only_tokens, COUNT(DISTINCT wtts.TOKEN_ADDRESS) AS total_unique_tokens_traded, (SUM(CASE WHEN wtts.has_buy_tx = 0 AND wtts.has_sell_tx = 1 THEN 1 ELSE 0 END) * 100.0 / NULLIF(COUNT(DISTINCT wtts.TOKEN_ADDRESS), 0)) AS percentage_sell_only_tokens FROM wallet_token_tx_type_summary wtts GROUP BY wtts.WALLET ), -- CTE 2: Calculate trade bounds for the "trades above 1 min" metric. wallet_token_trade_bounds AS ( SELECT WALLET, TOKEN_ADDRESS, MIN(BLOCK_TIMESTAMP) AS first_trade_per_token FROM transactions GROUP BY WALLET, TOKEN_ADDRESS ), -- CTE 3: Flag transactions that occur after the first minute of trading for that token. -- NOW ALSO INCLUDES FLAG FOR TRADES BETWEEN 1 AND 20 SECONDS transactions_with_duration_flag AS ( SELECT t.*, DATEDIFF('second', wttb.first_trade_per_token, t.BLOCK_TIMESTAMP) AS trade_duration_seconds, -- Calculate duration in seconds CASE WHEN DATEDIFF('minute', wttb.first_trade_per_token, t.BLOCK_TIMESTAMP) >= 1 THEN 1 ELSE 0 END AS is_trade_above_1_min, CASE -- New flag for trades between 1 and 20 seconds WHEN DATEDIFF('second', wttb.first_trade_per_token, t.BLOCK_TIMESTAMP) >= 1 AND DATEDIFF('second', wttb.first_trade_per_token, t.BLOCK_TIMESTAMP) <= 20 THEN 1 ELSE 0 END AS is_trade_between_1_and_20_seconds FROM transactions t JOIN wallet_token_trade_bounds wttb ON t.WALLET = wttb.WALLET AND t.TOKEN_ADDRESS = wttb.TOKEN_ADDRESS ), -- CTE 4: Aggregate initial data at the token level for every wallet, focusing only on REALIZED profit. prefilter_token_agg AS ( SELECT t.WALLET, t.TOKEN_ADDRESS, SUM(CASE WHEN t.txtype = 'buy' THEN 1 ELSE 0 END) AS BUY_TRANSACTION_COUNT, SUM(CASE WHEN t.txtype = 'buy' THEN -t.AMOUNT_TRADED ELSE 0 END) AS BUY_AMOUNT_SOL, (SUM(CASE WHEN t.txtype = 'sell' THEN t.AMOUNT_TRADED ELSE 0 END)) - (SUM(CASE WHEN t.txtype = 'buy' THEN -t.AMOUNT_TRADED ELSE 0 END)) AS REALIZED_PROFIT, SUM(t.TOKEN_AMOUNT_TRADED) AS TOKEN_LEFT_FOR_UNREALIZED, -- Keep TOKEN_LEFT for later COUNT(*) as TRANSACTION_COUNT FROM transactions t GROUP BY t.WALLET, t.TOKEN_ADDRESS ), -- CTE 5: Aggregate at the wallet level to perform the high-level filtering based on REALIZED profit. prefilter_wallet_agg AS ( SELECT pwa.WALLET, SUM(pwa.BUY_AMOUNT_SOL) AS TOTAL_BUY_SOL, SUM(pwa.REALIZED_PROFIT) AS TOTAL_PROFIT, -- Only REALIZED_PROFIT here (SUM(pwa.REALIZED_PROFIT) * 100.0 / NULLIF(SUM(pwa.BUY_AMOUNT_SOL), 0)) AS TOTAL_PNL, -- Only REALIZED_PNL here ROUND(SUM(CASE WHEN (pwa.REALIZED_PROFIT) > 0 THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(pwa.TOKEN_ADDRESS), 0), 2) AS WIN_RATE, COUNT(pwa.TOKEN_ADDRESS) AS TRADED_TOKEN_COUNT, SUM(pwa.TRANSACTION_COUNT) AS TOTAL_TRANSACTION_COUNT FROM prefilter_token_agg pwa WHERE pwa.BUY_AMOUNT_SOL > 0 GROUP BY pwa.WALLET ), -- CTE 6: Calculate the percentage of trades above 1 minute for each wallet. prefilter_duration_agg AS ( SELECT WALLET, (SUM(is_trade_above_1_min) * 100.0 / NULLIF(COUNT(*), 0)) AS PERCENTAGE_TRADES_ABOVE_1_MIN FROM transactions_with_duration_flag GROUP BY WALLET ), -- CTE 7: The core of the optimization. -- Create a list of wallets that meet the desired criteria. filtered_wallets AS ( SELECT pwa.WALLET FROM prefilter_wallet_agg pwa JOIN prefilter_duration_agg pda ON pwa.WALLET = pda.WALLET JOIN wallet_sell_only_percentage wsop ON pwa.WALLET = wsop.WALLET WHERE pwa.TRADED_TOKEN_COUNT BETWEEN 5 AND 1500 AND pwa.WIN_RATE > 0.2 AND pwa.TOTAL_PROFIT > 1 AND pwa.TOTAL_TRANSACTION_COUNT < 3000 AND pda.PERCENTAGE_TRADES_ABOVE_1_MIN BETWEEN 50 and 100 AND wsop.percentage_sell_only_tokens <= 30 ), -- ================================================================================================= -- FROM THIS POINT ON, ALL OPERATIONS ARE ON THE FILTERED_WALLETS SET. -- ================================================================================================= -- All transactions for our filtered wallets (first step for detailed calculations) final_transactions AS ( SELECT * FROM transactions_with_duration_flag WHERE WALLET IN (SELECT WALLET FROM filtered_wallets) ), -- Calculate latest token prices ONLY for tokens relevant to the filtered wallets. latest_token_prices AS ( SELECT t.TOKEN_ADDRESS, AVG(t.CALCULATED_TOKEN_PRICE) AS TOKEN_PRICE FROM final_transactions t WHERE t.BLOCK_TIMESTAMP = ( SELECT MAX(t2.BLOCK_TIMESTAMP) FROM final_transactions t2 WHERE t2.TOKEN_ADDRESS = t.TOKEN_ADDRESS ) GROUP BY t.TOKEN_ADDRESS ), -- Calculate next price and price impact ONLY for the filtered wallets' transactions. -- And include next transaction's price for simulated copy trade transactions_with_next_price AS ( SELECT t1.*, -- Use LAST_VALUE with a RANGE window for price 3 seconds later -- This finds the price of the last transaction within the next 3 seconds for the same token. LAST_VALUE(t1.CALCULATED_TOKEN_PRICE) OVER ( PARTITION BY TOKEN_ADDRESS ORDER BY BLOCK_TIMESTAMP RANGE BETWEEN CURRENT ROW AND INTERVAL '3 SECOND' FOLLOWING ) AS price_3_seconds_later, -- For price impact, use the immediate next transaction as originally defined -- Changed to use the 3-second window logic for price impact. ABS((LAST_VALUE(CALCULATED_TOKEN_PRICE) OVER (PARTITION BY TOKEN_ADDRESS ORDER BY BLOCK_TIMESTAMP RANGE BETWEEN CURRENT ROW AND INTERVAL '3 SECOND' FOLLOWING) - CALCULATED_TOKEN_PRICE) / CALCULATED_TOKEN_PRICE) * 100 AS PRICE_IMPACT_PERCENTAGE_PER_TX FROM final_transactions t1 ), -- NEW CTE: Pre-calculate MY_SIM_SELL_PRICE to avoid correlated subquery simulated_sell_prices AS ( SELECT t2.WALLET, t2.TOKEN_ADDRESS, t2.BLOCK_TIMESTAMP AS ORIGINAL_SELL_TIMESTAMP, -- Removed COALESCE: Now strictly uses price_3_seconds_later. If NULL, MY_SIM_SELL_PRICE_CALC will be NULL. t2.price_3_seconds_later AS MY_SIM_SELL_PRICE_CALC FROM transactions_with_next_price t2 WHERE t2.txtype = 'sell' AND (t2.WALLET, t2.TOKEN_ADDRESS, t2.BLOCK_TIMESTAMP) IN ( SELECT tba_inner.WALLET, tba_inner.TOKEN_ADDRESS, tba_inner.MY_ORIGINAL_SELL_TIMESTAMP FROM ( SELECT WALLET, TOKEN_ADDRESS, -- Removed COALESCE: Now strictly uses price_3_seconds_later. If NULL, MY_SIM_BUY_PRICE will be NULL. MIN(CASE WHEN txtype = 'buy' THEN price_3_seconds_later ELSE NULL END) AS MY_SIM_BUY_PRICE, MIN(CASE WHEN txtype = 'buy' THEN BLOCK_TIMESTAMP ELSE NULL END) AS MY_ORIGINAL_BUY_TIMESTAMP, MIN(CASE WHEN txtype = 'sell' THEN BLOCK_TIMESTAMP ELSE NULL END) AS MY_ORIGINAL_SELL_TIMESTAMP, MAX(CASE WHEN txtype = 'sell' THEN 1 ELSE 0 END) AS HAS_ORIGINAL_WALLET_SOLD FROM transactions_with_next_price GROUP BY WALLET, TOKEN_ADDRESS HAVING HAS_ORIGINAL_WALLET_SOLD = 1 -- Only consider tokens where there was a sell ) tba_inner WHERE tba_inner.MY_ORIGINAL_SELL_TIMESTAMP IS NOT NULL ) ), -- The same detailed token aggregation as the original query, now on the filtered set, -- and re-introducing UNREALIZED_PROFIT calculations. trades_by_token_agg AS ( SELECT t.WALLET, t.TOKEN_ADDRESS, COUNT(*) AS TRANSACTION_COUNT, SUM(CASE WHEN t.txtype = 'buy' THEN 1 ELSE 0 END) AS BUY_TRANSACTION_COUNT, SUM(CASE WHEN t.txtype = 'buy' THEN -t.AMOUNT_TRADED ELSE 0 END) AS BUY_AMOUNT_SOL, SUM(CASE WHEN t.txtype = 'sell' THEN t.AMOUNT_TRADED ELSE 0 END) AS SELL_AMOUNT_SOL, SUM(t.TOKEN_AMOUNT_TRADED) AS TOKEN_LEFT, SUM(CASE WHEN t.txtype = 'buy' THEN t.TOKEN_AMOUNT_TRADED ELSE 0 END) AS TOKEN_BUY_AMOUNT, SUM(CASE WHEN t.txtype = 'sell' THEN -t.TOKEN_AMOUNT_TRADED ELSE 0 END) AS TOKEN_SELL_AMOUNT, (SUM(CASE WHEN t.txtype = 'sell' THEN t.AMOUNT_TRADED ELSE 0 END)) - (SUM(CASE WHEN t.txtype = 'buy' THEN -t.AMOUNT_TRADED ELSE 0 END)) AS REALIZED_PROFIT, IFNULL((SUM(t.TOKEN_AMOUNT_TRADED) * latest.TOKEN_PRICE), 0) AS UNREALIZED_PROFIT, MIN(t.BLOCK_TIMESTAMP) AS first_trade_per_token, MAX(t.BLOCK_TIMESTAMP) AS last_trade_per_token, AVG(t.PRICE_IMPACT_PERCENTAGE_PER_TX) AS AVG_TOKEN_PRICE_IMPACT_PERCENTAGE, AVG(CASE WHEN t.txtype = 'buy' THEN t.PRICE_IMPACT_PERCENTAGE_PER_TX ELSE NULL END) AS AVG_BUY_PRICE_IMPACT_PERCENTAGE, AVG(CASE WHEN t.txtype = 'sell' THEN t.PRICE_IMPACT_PERCENTAGE_PER_TX ELSE NULL END) AS AVG_SELL_PRICE_IMPACT_PERCENTAGE, -- **SIMULATED COPY TRADE LOGIC START** -- MY_SIM_BUY_PRICE now uses price_3_seconds_later without fallback MIN(CASE WHEN t.txtype = 'buy' THEN t.price_3_seconds_later ELSE NULL END) AS MY_SIM_BUY_PRICE, MIN(CASE WHEN t.txtype = 'buy' THEN t.BLOCK_TIMESTAMP ELSE NULL END) AS MY_ORIGINAL_BUY_TIMESTAMP, MIN(CASE WHEN t.txtype = 'sell' THEN t.BLOCK_TIMESTAMP ELSE NULL END) AS MY_ORIGINAL_SELL_TIMESTAMP, MAX(CASE WHEN t.txtype = 'sell' THEN 1 ELSE 0 END) AS HAS_ORIGINAL_WALLET_SOLD -- **SIMULATED COPY TRADE LOGIC END** FROM transactions_with_next_price t LEFT JOIN latest_token_prices latest ON t.TOKEN_ADDRESS = latest.TOKEN_ADDRESS GROUP BY t.WALLET, t.TOKEN_ADDRESS, latest.TOKEN_PRICE HAVING BUY_AMOUNT_SOL > 0 AND TOKEN_LEFT >= 0 AND ABS(UNREALIZED_PROFIT / BUY_AMOUNT_SOL) <= 15 ), -- Final calculations per token (PNL %, hold time, etc.) trades_by_token_final AS ( SELECT tba.WALLET, tba.TOKEN_ADDRESS, tba.TRANSACTION_COUNT, tba.BUY_TRANSACTION_COUNT, tba.BUY_AMOUNT_SOL, tba.SELL_AMOUNT_SOL, tba.TOKEN_LEFT, tba.TOKEN_BUY_AMOUNT, tba.TOKEN_SELL_AMOUNT, tba.REALIZED_PROFIT, tba.UNREALIZED_PROFIT, tba.first_trade_per_token, tba.last_trade_per_token, tba.AVG_TOKEN_PRICE_IMPACT_PERCENTAGE, tba.AVG_BUY_PRICE_IMPACT_PERCENTAGE, tba.AVG_SELL_PRICE_IMPACT_PERCENTAGE, tba.MY_SIM_BUY_PRICE, tba.HAS_ORIGINAL_WALLET_SOLD, tba.MY_ORIGINAL_BUY_TIMESTAMP, tba.MY_ORIGINAL_SELL_TIMESTAMP, ssp.MY_SIM_SELL_PRICE_CALC AS MY_SIM_SELL_PRICE, -- Uses the strictly price_3_seconds_later value ROUND((tba.TOKEN_LEFT * 100.0) / NULLIF(tba.TOKEN_BUY_AMOUNT, 0), 2) AS PERCENT_BOUGHT_TOKEN_HELD, (tba.REALIZED_PROFIT + tba.UNREALIZED_PROFIT) * 100.0 / NULLIF(tba.BUY_AMOUNT_SOL, 0) AS TOKEN_PNL_PERCENTAGE, CASE WHEN tba.TOKEN_LEFT = 0 THEN DATEDIFF('minute', tba.first_trade_per_token, tba.last_trade_per_token) ELSE DATEDIFF('minute', tba.first_trade_per_token, CURRENT_TIMESTAMP()) END AS TOKEN_HOLD_TIME_MINUTES, -- **SIMULATED COPY TRADE PNL PER TOKEN START - ONLY IF HAS_ORIGINAL_WALLET_SOLD** CASE -- Now, if MY_SIM_BUY_PRICE or MY_SIM_SELL_PRICE_CALC is NULL (due to price_3_seconds_later being NULL), -- this entire CASE statement will fall to ELSE 0, skipping the calculation for that trade. WHEN tba.MY_SIM_BUY_PRICE IS NOT NULL AND tba.HAS_ORIGINAL_WALLET_SOLD = 1 AND ssp.MY_SIM_SELL_PRICE_CALC IS NOT NULL THEN (0.1 / tba.MY_SIM_BUY_PRICE) * ssp.MY_SIM_SELL_PRICE_CALC - 0.1 ELSE 0 END AS MY_SIM_TOKEN_PNL_PER_TOKEN -- **SIMULATED COPY TRADE PNL PER TOKEN END** FROM trades_by_token_agg tba LEFT JOIN latest_token_prices ltp ON tba.TOKEN_ADDRESS = ltp.TOKEN_ADDRESS LEFT JOIN simulated_sell_prices ssp ON tba.WALLET = ssp.WALLET AND tba.TOKEN_ADDRESS = ssp.TOKEN_ADDRESS AND tba.MY_ORIGINAL_SELL_TIMESTAMP = ssp.ORIGINAL_SELL_TIMESTAMP ), -- Final summary metrics for the wallet (trade times, etc.) wallet_summary_metrics AS ( SELECT WALLET, MIN(BLOCK_TIMESTAMP) AS first_trade_timestamp, MAX(BLOCK_TIMESTAMP) AS last_trade_timestamp, COUNT(*) AS total_wallet_transactions, SUM(is_trade_above_1_min) AS total_wallet_trades_above_1_min, SUM(is_trade_between_1_and_20_seconds) AS total_wallet_trades_1_to_20_sec FROM final_transactions GROUP BY WALLET ), -- ================================================================================================= -- CTEs for Daily PnL Fluctuation -- (These were correctly placed and are still valid after final_transactions) -- ================================================================================================= -- Transactions for only filtered wallets, grouped by day daily_transactions_filtered_wallets AS ( SELECT DATE_TRUNC('day', BLOCK_TIMESTAMP) AS TRADE_DATE, WALLET, TOKEN_ADDRESS, SUM(AMOUNT_TRADED) AS DAILY_AMOUNT_TRADED FROM final_transactions -- Use final_transactions here GROUP BY 1, 2, 3 ), -- Calculate daily realized PnL per wallet daily_realized_pnl AS ( SELECT TRADE_DATE, WALLET, SUM(DAILY_AMOUNT_TRADED) AS DAILY_REALIZED_PNL FROM daily_transactions_filtered_wallets GROUP BY TRADE_DATE, WALLET ), -- Summarize daily PnL by counting positive/negative/zero days daily_pnl_summary AS ( SELECT WALLET, COUNT(DISTINCT TRADE_DATE) AS TOTAL_TRADE_DAYS_DAILY, SUM(CASE WHEN DAILY_REALIZED_PNL > 0 THEN 1 ELSE 0 END) AS POSITIVE_PNL_DAYS, SUM(CASE WHEN DAILY_REALIZED_PNL < 0 THEN 1 ELSE 0 END) AS NEGATIVE_PNL_DAYS, SUM(CASE WHEN DAILY_REALIZED_PNL = 0 THEN 1 ELSE 0 END) AS ZERO_PNL_DAYS FROM daily_realized_pnl GROUP BY WALLET ) -- Final SELECT SELECT t.WALLET, COUNT(t.TOKEN_ADDRESS) AS TRADED_TOKEN_COUNT, SUM(t.TRANSACTION_COUNT) AS TOTAL_TRANSACTION_COUNT, SUM(t.BUY_AMOUNT_SOL) AS TOTAL_BUY_SOL, SUM(t.SELL_AMOUNT_SOL) AS TOTAL_SELL_SOL, SUM(t.REALIZED_PROFIT) AS TOTAL_REALIZED_PROFIT, SUM(t.UNREALIZED_PROFIT) AS TOTAL_UNREALIZED_PROFIT, (SUM(t.REALIZED_PROFIT) + SUM(t.UNREALIZED_PROFIT)) AS TOTAL_PROFIT, (SUM(t.REALIZED_PROFIT) + SUM(t.UNREALIZED_PROFIT)) * 100.0 / NULLIF(SUM(t.BUY_AMOUNT_SOL), 0) AS TOTAL_PNL, ROUND( SUM( CASE WHEN (t.REALIZED_PROFIT + t.UNREALIZED_PROFIT) > 0 THEN 1 ELSE 0 END ) * 1.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS WIN_RATE, ROUND( (ws.total_wallet_trades_above_1_min * 1.0 / NULLIF(ws.total_wallet_transactions, 0)) * 100, 2 ) AS PERCENTAGE_TRADES_ABOVE_1_MIN, ROUND( (ws.total_wallet_trades_1_to_20_sec * 1.0 / NULLIF(ws.total_wallet_transactions, 0)) * 100, 2 ) AS PERCENTAGE_TRADES_1_TO_20_SEC, DATEDIFF('day', ws.first_trade_timestamp, CURRENT_DATE()) AS TOTAL_TRADE_DAYS, dps.TOTAL_TRADE_DAYS_DAILY, DATEDIFF('day', ws.first_trade_timestamp, CURRENT_DATE()) - dps.TOTAL_TRADE_DAYS_DAILY AS INACTIVE_DAYS, ROUND( (dps.POSITIVE_PNL_DAYS * 1.0 / NULLIF(dps.TOTAL_TRADE_DAYS_DAILY, 0)) * 100, 2 ) AS DAILY_PNL_POSITIVE_PERCENTAGE, dps.POSITIVE_PNL_DAYS AS DAILY_PNL_POSITIVE_DAYS, dps.NEGATIVE_PNL_DAYS AS DAILY_PNL_NEGATIVE_DAYS, dps.ZERO_PNL_DAYS AS DAILY_PNL_ZERO_DAYS, ROUND( COUNT(CASE WHEN t.TRANSACTION_COUNT <= 5 THEN t.TOKEN_ADDRESS END) * 100.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS PERCENT_TOKENS_LE_5_TRADES, ROUND( COUNT(CASE WHEN t.TRANSACTION_COUNT > 5 AND t.TRANSACTION_COUNT < 20 THEN t.TOKEN_ADDRESS END) * 100.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS PERCENT_TOKENS_BTWN_5_AND_20_TRADES, ROUND( COUNT(CASE WHEN t.TRANSACTION_COUNT >= 20 THEN t.TOKEN_ADDRESS END) * 100.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS PERCENT_TOKENS_GE_20_TRADES, ROUND( COUNT(CASE WHEN t.PERCENT_BOUGHT_TOKEN_HELD > 50 THEN t.TOKEN_ADDRESS END) * 100.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS PERCENT_TOKENS_HOLDING_MAJORITY_POSITION, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE > 100 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_GT_100_PERCENT, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE > 500 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_GT_500_PERCENT, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE >= -99 AND t.TOKEN_PNL_PERCENTAGE <= -50 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_NEG_50_TO_99_PERCENT, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE > -50 AND t.TOKEN_PNL_PERCENTAGE < -1 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_NEG_1_TO_50_PERCENT, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE >= 1 AND t.TOKEN_PNL_PERCENTAGE <= 50 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_POS_1_TO_50_PERCENT, COUNT(CASE WHEN t.TOKEN_PNL_PERCENTAGE >= 50 AND t.TOKEN_PNL_PERCENTAGE < 99 THEN t.TOKEN_ADDRESS END) AS NUM_TOKENS_PNL_POS_50_TO_99_PERCENT, ROUND( COUNT(CASE WHEN t.TOKEN_ADDRESS != 'So11111111111111111111111111111111111111112' AND t.TOKEN_SELL_AMOUNT > t.TOKEN_BUY_AMOUNT THEN t.TOKEN_ADDRESS END) * 100.0 / NULLIF(COUNT(CASE WHEN t.TOKEN_ADDRESS != 'So11111111111111111111111111111111111111112' THEN t.TOKEN_ADDRESS END), 0) ) AS PERCENT_NON_SOL_SOLD_GT_BOUGHT, -- CORRECTED LINE ROUND(AVG(t.TOKEN_HOLD_TIME_MINUTES), 2) AS AVG_HOLD_TIME_MINUTES, ROUND(SUM(t.BUY_AMOUNT_SOL) / NULLIF(SUM(t.BUY_TRANSACTION_COUNT), 0), 2) AS AVG_BUY_AMOUNT_PER_TRADE_SOL, DATEDIFF('hour', ws.last_trade_timestamp, CURRENT_TIMESTAMP()) AS LAST_TRADE_HOURS_AGO, ROUND(AVG(t.AVG_TOKEN_PRICE_IMPACT_PERCENTAGE), 2) AS AVG_PRICE_IMPACT_PERCENTAGE, ROUND(SUM(t.AVG_BUY_PRICE_IMPACT_PERCENTAGE * t.BUY_TRANSACTION_COUNT) / NULLIF(SUM(t.BUY_TRANSACTION_COUNT), 0), 2) AS AVG_WALLET_BUY_PRICE_IMPACT, ROUND(SUM(t.AVG_SELL_PRICE_IMPACT_PERCENTAGE * (t.TRANSACTION_COUNT - t.BUY_TRANSACTION_COUNT)) / NULLIF(SUM(t.TRANSACTION_COUNT - t.BUY_TRANSACTION_COUNT), 0), 2) AS AVG_WALLET_SELL_PRICE_IMPACT, ROUND(AVG(t.TOKEN_PNL_PERCENTAGE), 2) AS AVG_TOKEN_PNL_PERCENTAGE, -- **SIMULATED COPY TRADING METRICS (3-second delay only)** SUM(t.MY_SIM_TOKEN_PNL_PER_TOKEN) AS MY_SIM_TOTAL_PNL_SOL, 1.0 + SUM(t.MY_SIM_TOKEN_PNL_PER_TOKEN) AS MY_FINAL_SOL_BALANCE, -- Starting with 1 SOL ROUND( SUM(CASE WHEN t.MY_SIM_TOKEN_PNL_PER_TOKEN > 0 THEN 1 ELSE 0 END) * 1.0 / NULLIF(COUNT(t.TOKEN_ADDRESS), 0), 2 ) AS MY_SIM_WIN_RATE FROM trades_by_token_final t JOIN wallet_summary_metrics ws ON t.WALLET = ws.WALLET JOIN daily_pnl_summary dps ON t.WALLET = dps.WALLET GROUP BY t.WALLET, ws.first_trade_timestamp, ws.last_trade_timestamp, ws.total_wallet_transactions, ws.total_wallet_trades_above_1_min, ws.total_wallet_trades_1_to_20_sec, dps.TOTAL_TRADE_DAYS_DAILY, dps.POSITIVE_PNL_DAYS, dps.NEGATIVE_PNL_DAYS, dps.ZERO_PNL_DAYS HAVING TOTAL_TRANSACTION_COUNT < 3000 AND TRADED_TOKEN_COUNT BETWEEN 5 AND 1500 AND MY_SIM_WIN_RATE > 0.6 AND PERCENT_TOKENS_GE_20_TRADES < 15 AND PERCENTAGE_TRADES_ABOVE_1_MIN BETWEEN 50 AND 100 AND TOTAL_TRADE_DAYS > 1 AND TOTAL_PROFIT >= 1 AND MY_SIM_TOTAL_PNL_SOL >= 1 AND LAST_TRADE_HOURS_AGO <= 120 AND NUM_TOKENS_PNL_GT_100_PERCENT >= 1 AND NUM_TOKENS_PNL_POS_50_TO_99_PERCENT >= 1 AND AVG_BUY_AMOUNT_PER_TRADE_SOL < 10 AND AVG_BUY_AMOUNT_PER_TRADE_SOL > 0.03 AND AVG_TOKEN_PNL_PERCENTAGE >= 30 ORDER BY WIN_RATE DESC, TOTAL_PNL DESC, TRADED_TOKEN_COUNT DESC

bussyslayer