WITH datasales AS ( SELECT block_time, tx_id, t.trader_id AS wallet, token_sold_mint_address AS token_address, CASE WHEN token_sold_symbol IS NULL THEN token_sold_mint_address ELSE token_sold_symbol END AS asset, token_sold_amount AS amount, amount_usd AS usd_volume, 0 AS token_price, amount_usd / NULLIF(token_sold_amount, 0) AS tp, 'sell' AS action FROM dex_solana.trades t WHERE token_sold_mint_address NOT IN ( 'So11111111111111111111111111111111111111112', 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' ) AND token_sold_symbol NOT IN ('WETH', 'USDT', 'USDC') AND block_time >= CURRENT_DATE - INTERVAL '{{Interval}}' DAY UNION ALL SELECT block_time, tx_id, t.trader_id AS wallet, token_bought_mint_address AS token_address, CASE WHEN token_bought_symbol IS NULL THEN token_bought_mint_address ELSE token_bought_symbol END AS asset, token_bought_amount AS amount, - amount_usd AS usd_volume, amount_usd / token_bought_amount AS token_price, amount_usd / NULLIF(token_bought_amount, 0) AS tp, 'buy' AS action FROM dex_solana.trades t WHERE token_bought_mint_address NOT IN ( 'So11111111111111111111111111111111111111112', 'DezXAZ8z7PnrnRJjz3wXBoRgixCa6xjnB7YaB1pPB263' ) AND token_bought_symbol NOT IN ('WETH', 'USDT', 'USDC') AND block_time >= CURRENT_DATE - INTERVAL '{{Interval}}' DAY ), token_roi AS ( SELECT wallet, token_address, asset, ROUND( SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) - SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ), 2 ) AS net_profit, ROUND( SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ), 2 ) AS total_invested, ROUND( ( SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) - SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) ) / NULLIF( SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ), 0 ), 4 ) * 100 AS roi FROM datasales GROUP BY wallet, token_address, asset ), losing_trades_roi AS ( SELECT wallet, AVG(roi) AS avg_roi_losing_trades FROM token_roi WHERE roi < 0 GROUP BY wallet ), winning_trades_roi AS ( SELECT wallet, AVG(roi) AS avg_roi_winning_trades FROM token_roi WHERE roi > 0 GROUP BY wallet ), roi_metrics AS ( SELECT tr.wallet, AVG(tr.roi) AS avg_roi, SUM(tr.roi) AS summated_roi -- Calculate the summated ROI for each wallet FROM token_roi tr GROUP BY tr.wallet ), buy_sell_match AS ( SELECT wallet, token_address, MAX( CASE WHEN action = 'buy' THEN 1 ELSE 0 END ) AS has_buy, MAX( CASE WHEN action = 'sell' THEN 1 ELSE 0 END ) AS has_sell FROM datasales GROUP BY wallet, token_address ), cumulative_buys_and_sells AS ( SELECT wallet, token_address, SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) AS total_spent, SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) AS total_earned FROM datasales GROUP BY wallet, token_address ), transaction_win_rate AS ( SELECT wallet, token_address, -- Consistency outcome based on original criteria CASE WHEN total_earned >= 2 * total_spent AND total_spent > 0 THEN 'Consistent' ELSE 'Not Consistent' END AS consistency_outcome, CASE WHEN total_earned >= 4 * total_spent AND total_spent > 0 THEN 'Jackpot' ELSE 'Not Jackpot' END AS jackpot_outcome, CASE WHEN total_earned < 0.5 * total_spent AND total_spent > 0 THEN 'Rugged' ELSE 'Not Rugged' END AS rugged_outcome, -- Win/Loss outcome for actual win rate calculation CASE WHEN total_earned > total_spent THEN 'Win' ELSE 'Loss' END AS win_loss_outcome, ROUND( (total_earned - total_spent) / NULLIF(total_spent, 0) * 100, 2 ) AS percent_return FROM cumulative_buys_and_sells ), -- Updated win_rate_per_wallet CTE to calculate both Average Win Rate and Consistency Rate win_rate_per_wallet AS ( SELECT wallet, -- Calculate Actual Win Rate ROUND( AVG( CASE WHEN win_loss_outcome = 'Win' THEN 1 ELSE 0 END ) * 100, 2 ) AS "Actual Win Rate", -- Calculate Consistency Rate ROUND( AVG( CASE WHEN consistency_outcome = 'Consistent' THEN 1 ELSE 0 END ) * 100, 2 ) AS "Consistency Rate", ROUND( AVG( CASE WHEN jackpot_outcome = 'Jackpot' THEN 1 ELSE 0 END ) * 100, 2 ) AS "Jackpot Rate", ROUND( AVG( CASE WHEN rugged_outcome = 'Rugged' THEN 1 ELSE 0 END ) * 100, 2 ) AS "Rugged Rate" FROM transaction_win_rate GROUP BY wallet ), aggregated_volumes AS ( SELECT wallet, SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) AS "Total Trading Spent", SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) AS "Total Net Profit/Loss", ( SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) - SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) ) AS "PnL" FROM datasales GROUP BY wallet ), unique_assets_per_wallet AS ( SELECT wallet, COUNT(DISTINCT token_address) AS "Total Unique Assets" FROM datasales WHERE block_time >= CURRENT_DATE - INTERVAL '{{Interval}}' DAY GROUP BY wallet ), trading_days_per_wallet AS ( SELECT wallet, COUNT(DISTINCT trading_day) AS "Trading Days", COUNT( DISTINCT CASE WHEN daily_net_profit > 0 THEN trading_day END ) AS "Profitable Trading Days" FROM ( SELECT wallet, DATE(block_time) AS trading_day, -- Ensure this alias is used consistently SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE - usd_volume -- Assume buying results in negative volume for profit calculation END ) AS daily_net_profit FROM datasales GROUP BY wallet, DATE(block_time) ) AS daily_profits GROUP BY wallet ), buy_sell_transactions_per_asset AS ( SELECT wallet, token_address, SUM( CASE WHEN action = 'buy' THEN 1 ELSE 0 END ) AS total_buys, SUM( CASE WHEN action = 'sell' THEN 1 ELSE 0 END ) AS total_sells FROM datasales GROUP BY wallet, token_address ), asset_transactions_summary AS ( SELECT wallet, token_address, SUM( CASE WHEN action = 'buy' THEN amount ELSE 0 END ) AS total_bought, SUM( CASE WHEN action = 'sell' THEN amount ELSE 0 END ) AS total_sold FROM datasales GROUP BY wallet, token_address ), average_sold_bought_ratio AS ( SELECT wallet, AVG( CASE WHEN total_bought = 0 THEN NULL ELSE total_sold / NULLIF(total_bought, 0) END ) AS avg_sold_bought_ratio FROM asset_transactions_summary GROUP BY wallet ), average_transactions_per_wallet AS ( SELECT wallet, AVG(total_buys) AS "Avg Number of Buys Per Asset", AVG(total_sells) AS "Avg Number of Sells Per Asset" FROM buy_sell_transactions_per_asset GROUP BY wallet ), average_win_loss_sizes AS ( SELECT ds.wallet, AVG( CASE WHEN ds.action = 'sell' AND ds.usd_volume > 0 THEN ds.usd_volume ELSE NULL END ) AS avg_win_size, AVG( CASE WHEN ds.action = 'buy' AND ds.usd_volume < 0 THEN ABS(ds.usd_volume) ELSE NULL END ) AS avg_loss_size FROM datasales ds JOIN buy_sell_match bsm ON ds.wallet = bsm.wallet AND ds.token_address = bsm.token_address GROUP BY ds.wallet ), win_loss_size_ratio AS ( SELECT wallet, -- Use 1 as the denominator if there are no losses, otherwise use the sum of losses total_wins / CASE WHEN total_losses = 0 THEN 1 ELSE total_losses END AS WinLossSizeRatio FROM ( SELECT wallet, SUM( CASE WHEN roi > 0 THEN roi ELSE 0 END ) AS total_wins, -- Sum of ROIs for winning trades SUM( CASE WHEN roi < 0 THEN ABS(roi) ELSE 0 END ) AS total_losses -- Sum of ROIs for losing trades, converted to positive FROM token_roi -- Assuming token_roi contains the ROI calculations per trade GROUP BY wallet ) AS win_loss_data ), stddev_metrics AS ( SELECT wallet, STDDEV(roi) AS stddev_roi, -- Standard deviation of all ROI STDDEV( CASE WHEN roi < 0 THEN roi ELSE NULL END ) AS stddev_negative_roi -- Standard deviation of negative ROI FROM token_roi GROUP BY wallet ), trading_activity AS ( SELECT wallet, SUM( CASE WHEN action = 'buy' THEN 1 ELSE 0 END ) * 1.0 / COUNT(DISTINCT DATE(block_time)) AS TradesPerDay FROM datasales GROUP BY wallet ), unique_assets_normalized AS ( SELECT wallet, -- Adjust the calculation to ensure higher unique assets result in higher scores 100 * ( -- Subtract each log-transformed value from the maximum log-transformed value in the dataset MAX( LOG( GREATEST(CAST("Total Unique Assets" AS DOUBLE), 1.0) + 1.0, EXP(1) ) ) OVER () - LOG( GREATEST(CAST("Total Unique Assets" AS DOUBLE), 1.0) + 1.0, EXP(1) ) ) / ( MAX( LOG( GREATEST(CAST("Total Unique Assets" AS DOUBLE), 1.0) + 1.0, EXP(1) ) ) OVER () - MIN( LOG( GREATEST(CAST("Total Unique Assets" AS DOUBLE), 1.0) + 1.0, EXP(1) ) ) OVER () ) AS NormalizedUniqueAssets FROM unique_assets_per_wallet ), recent_activity AS ( SELECT wallet, MAX(block_time) AS last_trade_date FROM datasales GROUP BY wallet ), win_loss_ratio_calculation AS ( SELECT wallet, AVG(avg_win_size) / NULLIF(AVG(avg_loss_size), 0) AS win_loss_ratio -- Hypothetical average calculation FROM average_win_loss_sizes GROUP BY wallet ), kelly_criterion AS ( SELECT wr.wallet, GREATEST( ( "Actual Win Rate" / 100 - (1 - "Actual Win Rate" / 100) / wlr.win_loss_ratio ) / 8, 0 ) AS "Invest %" FROM win_rate_per_wallet wr JOIN win_loss_ratio_calculation wlr ON wr.wallet = wlr.wallet ), daily_roi AS ( SELECT wallet, DATE(block_time) AS trade_date, SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) - SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) AS daily_net_profit, SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) AS daily_invested, ROUND( ( SUM( CASE WHEN action = 'sell' THEN usd_volume ELSE 0 END ) - SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ) ) / NULLIF( SUM( CASE WHEN action = 'buy' THEN - usd_volume ELSE 0 END ), 0 ), 4 ) * 100 AS daily_roi_percent FROM datasales GROUP BY wallet, DATE(block_time) ), summated_daily_roi AS ( SELECT wallet, SUM(daily_roi_percent) AS summated_roi_percent FROM daily_roi GROUP BY wallet ), VAR_calculation AS ( SELECT wallet, daily_roi_percent AS VAR_95 FROM ( SELECT wallet, daily_roi_percent, ROW_NUMBER() OVER ( PARTITION BY wallet ORDER BY daily_roi_percent ) as rn FROM daily_roi ) as ranked_rois WHERE rn = 2 -- This value should be adjusted based on your actual percentile calculation outside SQL ), agg_roi AS ( SELECT wallet, SUM( CASE WHEN roi > 0 THEN roi ELSE 0 END ) AS total_positive_roi, SUM( CASE WHEN roi < 0 THEN roi ELSE 0 END ) AS total_negative_roi FROM token_roi GROUP BY wallet ), cumulative_roi AS ( SELECT wallet, trade_date, SUM(daily_roi_percent) OVER ( PARTITION BY wallet ORDER BY trade_date ASC ) AS cumulative_roi_percent FROM daily_roi ), historical_peaks AS ( SELECT wallet, trade_date, MAX(cumulative_roi_percent) OVER ( PARTITION BY wallet ORDER BY trade_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS historical_peak_roi FROM cumulative_roi ), drawdowns AS ( SELECT hp.wallet, hp.trade_date, -- Calculate the percentage difference from the peak; no need to enforce non-negativity here 100 * ( 1 - (cumulative_roi_percent / historical_peak_roi) ) AS drawdown_percent FROM historical_peaks hp JOIN cumulative_roi cr ON hp.wallet = cr.wallet AND hp.trade_date = cr.trade_date WHERE historical_peak_roi > 0 ), max_drawdowns AS ( SELECT wallet, MAX(drawdown_percent) AS max_drawdown_percent FROM drawdowns GROUP BY wallet ), wallet_metrics AS ( SELECT av.wallet, av."Total Trading Spent" / NULLIF(uapw."Total Unique Assets", 0) AS "Average Buy Sum", av."Total Trading Spent", av."Total Net Profit/Loss", rm.avg_roi AS "%ROI", rm.summated_roi AS "Summated ROI", wrpw."Actual Win Rate", wrpw."Consistency Rate", wrpw."Jackpot Rate", wrpw."Rugged Rate", uapw."Total Unique Assets", tdw."Trading Days", tdw."Profitable Trading Days", ta.TradesPerDay, atpw."Avg Number of Buys Per Asset", atpw."Avg Number of Sells Per Asset", WinLossSizeRatio, asbr.avg_sold_bought_ratio, aws.avg_win_size, -- Included from a corrected join or subquery aws.avg_loss_size, -- Included from a corrected join or subquery COALESCE(ltr.avg_roi_losing_trades, 0) AS "Avg ROI Losing Trades", wtr.avg_roi_winning_trades AS "Avg ROI Winning Trades", uan.NormalizedUniqueAssets, kc."Invest %", sdri.summated_roi_percent, vc.VAR_95, md.max_drawdown_percent AS "Maximum Drawdown Percent", ( (0.40 * rm.avg_roi) + (0.50 * wrpw."Actual Win Rate") + (0.10 * (uan.NormalizedUniqueAssets) / 1) ) AS "Wallet Power", sm.stddev_roi AS "StdDev ROI", COALESCE(sm.stddev_negative_roi, 0) AS "StdDev Negative ROI", CASE WHEN sm.stddev_roi > 0 THEN rm.avg_roi / sm.stddev_roi ELSE NULL END AS "Sharpe Ratio", CASE WHEN COALESCE(sm.stddev_negative_roi, 0) > 0 THEN rm.avg_roi / COALESCE(sm.stddev_negative_roi, 1) ELSE 0 END AS "Sortino Ratio", ar.total_positive_roi, ar.total_negative_roi, CASE WHEN ABS(COALESCE(ar.total_negative_roi, 0)) > 0 THEN ( ar.total_positive_roi / ABS(COALESCE(ar.total_negative_roi, 1)) ) ELSE 0 END AS "Omega Ratio", -- Corrected expectancy calculation using avg_win_size and avg_loss_size ( (wrpw."Actual Win Rate" / 100) * aws.avg_win_size ) - ( (1 - (wrpw."Actual Win Rate" / 100)) * aws.avg_loss_size ) AS "Expectancy %" FROM aggregated_volumes av JOIN roi_metrics rm ON av.wallet = rm.wallet JOIN agg_roi ar ON av.wallet = ar.wallet JOIN win_rate_per_wallet wrpw ON av.wallet = wrpw.wallet JOIN unique_assets_per_wallet uapw ON av.wallet = uapw.wallet JOIN unique_assets_normalized uan ON av.wallet = uan.wallet JOIN trading_days_per_wallet tdw ON av.wallet = tdw.wallet JOIN average_transactions_per_wallet atpw ON av.wallet = atpw.wallet JOIN trading_activity ta ON av.wallet = ta.wallet JOIN recent_activity ra ON av.wallet = ra.wallet JOIN average_sold_bought_ratio asbr ON av.wallet = asbr.wallet JOIN kelly_criterion kc ON av.wallet = kc.wallet JOIN summated_daily_roi sdri ON av.wallet = sdri.wallet JOIN win_loss_size_ratio wlsr ON av.wallet = wlsr.wallet LEFT JOIN VAR_calculation vc ON av.wallet = vc.wallet LEFT JOIN losing_trades_roi ltr ON av.wallet = ltr.wallet LEFT JOIN winning_trades_roi wtr ON av.wallet = wtr.wallet LEFT JOIN stddev_metrics sm ON av.wallet = sm.wallet LEFT JOIN max_drawdowns md ON av.wallet = md.wallet -- Ensure this join correctly brings in avg_win_size and avg_loss_size for each wallet JOIN average_win_loss_sizes aws ON av.wallet = aws.wallet WHERE ra.last_trade_date >= CURRENT_DATE - INTERVAL '{{Active Interval}}' DAY ) SELECT '' || wallet || '' AS wallet, 'BirdEye Profile' AS birdeye, "Average Buy Sum", "Total Trading Spent", "Total Net Profit/Loss", "%ROI", "Summated ROI", -- Include the new "Summated ROI" metric in the final SELECT "Actual Win Rate", "Consistency Rate", "Jackpot Rate", "Rugged Rate", "Total Unique Assets", CAST("Profitable Trading Days" AS VARCHAR) || ' / ' || CAST("Trading Days" AS VARCHAR) AS inprofittotaldays, "Trading Days", "Profitable Trading Days", "Avg Number of Buys Per Asset", "Avg Number of Sells Per Asset", avg_sold_bought_ratio AS "Average Sold/Bought Ratio", WinLossSizeRatio AS "Win to Loss Size Ratio", "Avg ROI Losing Trades", "Avg ROI Winning Trades", ( (("Actual Win Rate" / 100) * avg_win_size) - ((1 - ("Actual Win Rate" / 100)) * avg_loss_size) ) AS "EV", ("Expectancy %" / avg_loss_size) AS "Expectation", "Invest %" AS "Kelly Criterion Invest %", "VAR_95" AS "Value at Risk (95%)", "Maximum Drawdown Percent", "Wallet Power", "StdDev ROI", "StdDev Negative ROI", "Sharpe Ratio", "Sortino Ratio", "Omega Ratio" FROM wallet_metrics WHERE "Total Unique Assets" > 4 AND "Average Buy Sum" > 1 AND "Average Buy Sum" < 75 AND "Actual Win Rate" > 70 AND avg_sold_bought_ratio < 1.25 AND "Avg Number of Buys Per Asset" <= 1.6 AND "%ROI" > 50 AND "Sharpe Ratio" >= 0.1 AND "Expectancy %" > 1.1 ORDER BY "Expectancy %" DESC;

Query	Creator	Created
Getting Started	findingalpha	Mar 9, 2024
2024-03-09 11:59 AM	findingalpha	Mar 9, 2024

findingalpha