with tgt_addr_obs as( SELECT DISTINCT FROM_ADDRESS as address FROM ethereum.core.fact_transactions -- WHERE FROM_ADDRESS='0x8e3eedf2c8655c442f0c1cc0a58160250becf03c' where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') -- and NONCE=0 ), tgt_week as( SELECT *, DENSE_RANK() over( ORDER BY start_of_week ) as week_rn FROM ( SELECT date(BLOCK_TIMESTAMP) as date_str -- ,DATE_TRUNC('week', TO_DATE(BLOCK_TIMESTAMP)) AS start_of_week , CEIL( ROW_NUMBER() OVER ( ORDER BY DATE(BLOCK_TIMESTAMP) ) / 7 ) AS start_of_week FROM ethereum.core.fact_transactions -- WHERE FROM_ADDRESS='0x8e3eedf2c8655c442f0c1cc0a58160250becf03c' where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') group by date(BLOCK_TIMESTAMP) ) ), tgt_addr_obs_age as( SELECT a.FROM_ADDRESS as address, DATEDIFF( day, min(a.BLOCK_TIMESTAMP), date('{{analysis_date}}') ) as wallet_age FROM ethereum.core.fact_transactions a inner join tgt_addr_obs b on a.FROM_ADDRESS = b.address GROUP BY a.FROM_ADDRESS ), gas_use_obs as( SELECT FROM_ADDRESS, sum(TX_FEE) * {{eth_price_u}} as TX_FEE_AMOUNT FROM ethereum.core.fact_transactions where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') group by FROM_ADDRESS ), token_info as( SELECT TOKEN_OUT as token_address, min(BLOCK_TIMESTAMP) as first_token_buy_time FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= DATEADD( day, - {{gap_day}} -30, '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= '{{analysis_date}}' AND TOKEN_OUT not in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) group by TOKEN_OUT ), block_amount_in_out_obs as( SELECT TOKEN_IN, TOKEN_OUT, BLOCK_NUMBER, BLOCK_TIMESTAMP, sum(AMOUNT_IN) as AMOUNT_IN, sum(AMOUNT_OUT) as AMOUNT_OUT FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= DATEADD( day, - {{gap_day}} -1, '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= '{{analysis_date}}' AND AMOUNT_OUT > 0 GROUP BY TOKEN_IN, TOKEN_OUT, BLOCK_NUMBER, BLOCK_TIMESTAMP ), block_inout_ratio_obs as( SELECT *, case when AMOUNT_OUT_5_BLOCK = 0 then 0 else AMOUNT_IN_5_BLOCK / AMOUNT_OUT_5_BLOCK end as inout_ratio_5_block FROM ( SELECT TOKEN_IN, TOKEN_OUT, BLOCK_NUMBER, BLOCK_TIMESTAMP, SUM(AMOUNT_IN) OVER ( PARTITION BY TOKEN_IN, TOKEN_OUT ORDER BY BLOCK_NUMBER DESC ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING ) AS AMOUNT_IN_5_BLOCK, SUM(AMOUNT_OUT) OVER ( PARTITION BY TOKEN_IN, TOKEN_OUT ORDER BY BLOCK_NUMBER DESC ROWS BETWEEN 5 PRECEDING AND 3 PRECEDING ) AS AMOUNT_OUT_5_BLOCK FROM block_amount_in_out_obs ) ), coin_msg_obs as( SELECT a.*, b.or_buyer_cnt, case when b.or_buyer_cnt <= 50 then 1 end as is_malicious_coin -- ,case when when or_seller_cnt <= 20 then 1 -- else 0 end as is_malicious_coin FROM ( SELECT TOKEN_IN as TOKEN, coalesce(COUNT(DISTINCT ORIGIN_FROM_ADDRESS), 0) as or_seller_cnt FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') -- AND ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' group by TOKEN_IN ) a full outer join ( SELECT TOKEN_OUT as TOKEN, coalesce(COUNT(DISTINCT ORIGIN_FROM_ADDRESS), 0) as or_buyer_cnt FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') -- AND ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' group by TOKEN_OUT ) b on a.TOKEN = b.TOKEN ), swap_deal_obs as( SELECT TX_HASH, TOKEN_IN, BUY_TOKEN, SYMBOL_IN, TOKEN_OUT, SELL_TOKEN, SYMBOL_OUT, CONTRACT_ADDRESS, BLOCK_TIMESTAMP, BLOCK_NUMBER, sum(AMOUNT_BUY_USD) as AMOUNT_BUY_USD, sum(AMOUNT_IN) as AMOUNT_IN, sum(AMOUNT_SELL_USD) as AMOUNT_SELL_USD, max(inout_ratio) as inout_ratio, sum(AMOUNT_OUT) as AMOUNT_OUT FROM ( SELECT TX_HASH, TOKEN_IN, ( case when TOKEN_IN = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then AMOUNT_IN * {{eth_price_u}} when TOKEN_IN in ( '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) then AMOUNT_IN else 0 end ) as AMOUNT_BUY_USD, ( case when TOKEN_IN in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) then TOKEN_OUT else null end ) as BUY_TOKEN, AMOUNT_IN, SYMBOL_IN, TOKEN_OUT, ( case when TOKEN_OUT = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then AMOUNT_OUT * {{eth_price_u}} when TOKEN_OUT in ( '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) then AMOUNT_OUT else 0 end ) as AMOUNT_SELL_USD, ( case when TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) then TOKEN_IN else null end ) as SELL_TOKEN, case when AMOUNT_OUT = 0 then 0 else AMOUNT_IN / AMOUNT_OUT end as inout_ratio, AMOUNT_OUT, SYMBOL_OUT, CONTRACT_ADDRESS, BLOCK_TIMESTAMP, BLOCK_NUMBER FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= DATEADD( day, - '{{gap_day}}', '{{analysis_date}}' ) AND date(BLOCK_TIMESTAMP) <= DATEADD(day, -1, '{{analysis_date}}') -- AND ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' and ( TOKEN_IN in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) OR TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) ) ) GROUP BY TX_HASH, TOKEN_IN, BUY_TOKEN, SYMBOL_IN, TOKEN_OUT, SELL_TOKEN, SYMBOL_OUT, CONTRACT_ADDRESS, BLOCK_TIMESTAMP, BLOCK_NUMBER ), trade_msg_obs_old_obs as( SELECT a.*, c.inout_ratio_5_block, case when inout_ratio = 0 then 0 when a.TOKEN_IN in ( '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ) AND (inout_ratio_5_block / inout_ratio) >= 5 THEN 0 else a.AMOUNT_BUY_USD * (inout_ratio_5_block / inout_ratio) end as AMOUNT_BUY_USD_follow, case when inout_ratio = 0 then 0 when a.TOKEN_OUT in ( '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ) AND (inout_ratio_5_block / inout_ratio) < 30 THEN a.AMOUNT_SELL_USD * (inout_ratio_5_block / inout_ratio) ELSE 0 end as AMOUNT_SELL_USD_follow FROM ( SELECT a.*, b.FROM_ADDRESS FROM ( select * FROM swap_deal_obs ) a left join ethereum.core.fact_transactions b on a.TX_HASH = b.TX_HASH ) a inner join tgt_addr_obs b on a.FROM_ADDRESS = b.address left join ( SELECT BLOCK_NUMBER, TOKEN_IN, TOKEN_OUT, inout_ratio_5_block from block_inout_ratio_obs ) c on ( a.BLOCK_NUMBER = c.BLOCK_NUMBER AND a.TOKEN_IN = c.TOKEN_IN AND a.TOKEN_OUT = c.TOKEN_OUT ) ), txn_transfer_out_obs as( SELECT TX_HASH, TO_ADDRESS, sum(AMOUNT_SELL_USD1) as AMOUNT_SELL_USD1, min(CONTRACT_ADDRESS) as min_token_address, max(CONTRACT_ADDRESS) as max_token_address FROM ( SELECT TX_HASH, TO_ADDRESS, CONTRACT_ADDRESS, SUM(RAW_AMOUNT) as RAW_AMOUNT, SUM(AMOUNT_SELL_USD1) as AMOUNT_SELL_USD1 FROM ( SELECT TX_HASH, TO_ADDRESS, RAW_AMOUNT, ( case when CONTRACT_ADDRESS = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then RAW_AMOUNT /(1e18) * {{eth_price_u}} when CONTRACT_ADDRESS in ( '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) then RAW_AMOUNT /(1e6) else 0 end ) as AMOUNT_SELL_USD1, CONTRACT_ADDRESS FROM ethereum.core.fact_token_transfers WHERE CONTRACT_ADDRESS in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) AND date(BLOCK_TIMESTAMP) >= DATEADD(day, 0, '{{analysis_date}}') AND date(BLOCK_TIMESTAMP) <= DATEADD(day, 0, '{{analysis_date}}') UNION ALL SELECT TX_HASH, TO_ADDRESS, VALUE *(1e18) as RAW_AMOUNT, VALUE * {{eth_price_u}} as AMOUNT_SELL_USD1, '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' as CONTRACT_ADDRESS FROM ethereum.core.fact_traces WHERE date(BLOCK_TIMESTAMP) >= DATEADD(day, 0, '{{analysis_date}}') AND date(BLOCK_TIMESTAMP) <= DATEADD(day, 0, '{{analysis_date}}') ) GROUP BY TX_HASH, TO_ADDRESS, CONTRACT_ADDRESS ) a inner join tgt_addr_obs b on a.TO_ADDRESS = b.address GROUP BY TX_HASH, TO_ADDRESS ), trade_msg_obs as( SELECT a.*, b.AMOUNT_SELL_USD1, b.min_token_address, b.max_token_address FROM trade_msg_obs_old_obs a left join txn_transfer_out_obs b on a.FROM_ADDRESS = b.TO_ADDRESS and a.TX_HASH = b.TX_HASH and a.TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) ), simple_trade_obs as( SELECT * FROM ( select FROM_ADDRESS, TX_HASH, case when BUY_TOKEN is not null then BUY_TOKEN else SELL_TOKEN end as token_address, case when BUY_TOKEN is not null then SYMBOL_OUT else SYMBOL_IN end as token_symbol, case when BUY_TOKEN is not null then - AMOUNT_BUY_USD else ( case when AMOUNT_SELL_USD1 is not null then AMOUNT_SELL_USD1 else AMOUNT_SELL_USD end ) end as amount_usd, case when BUY_TOKEN is not null then - AMOUNT_BUY_USD_FOLLOW else AMOUNT_SELL_USD_FOLLOW end as amount_usd_follow, BLOCK_TIMESTAMP from trade_msg_obs -- WHERE FROM_ADDRESS='0x27874032c8b54b3a431400121aa963615df9c13f' order by FROM_ADDRESS, token_address, BLOCK_TIMESTAMP ) ), trader_token_info as( SELECT FROM_ADDRESS, sum(normal_token) as normal_token_cnt, count(DISTINCT token_address) as all_token_cnt, sum(normal_token) / count(DISTINCT token_address) as normal_token_ratio FROM ( SELECT *, case when is_buy = 1 and is_sell = 1 then 1 else 0 end as normal_token FROM ( SELECT FROM_ADDRESS, token_address, max( case when amount_usd < 0 then 1 else 0 end ) as is_buy, max( case when amount_usd > 0 then 1 else 0 end ) as is_sell FROM simple_trade_obs GROUP BY FROM_ADDRESS, token_address ) ) GROUP BY FROM_ADDRESS ), CumulativeSum_obs AS ( SELECT FROM_ADDRESS, TX_HASH, token_address, amount_usd, amount_usd_follow, BLOCK_TIMESTAMP, SUM(amount_usd) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ) AS cumsum_amount, SUM(amount_usd_follow) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ) AS cumsum_amount_follow, SUM( case when amount_usd < 0 then amount_usd else 0 end ) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ) AS cumsum_amount_buy, SUM( case when amount_usd > 0 then amount_usd else 0 end ) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ) AS cumsum_amount_sell FROM simple_trade_obs order by FROM_ADDRESS, token_address, BLOCK_TIMESTAMP ), MaxCumulativeSum_obs AS ( SELECT FROM_ADDRESS, token_address, TX_HASH, MAX(cumsum_amount) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS max_cumsum_amount, MAX(cumsum_amount_follow) OVER ( PARTITION BY FROM_ADDRESS, token_address ORDER BY BLOCK_TIMESTAMP ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING ) AS max_cumsum_amount_follow FROM CumulativeSum_obs ), simple_trade_obs_detail_obs_old_obs as( SELECT a.*, b.week_rn FROM ( SELECT * FROM ( SELECT a.*, date(a.BLOCK_TIMESTAMP) as date_str, CASE WHEN a.cumsum_amount > NVL(b.max_cumsum_amount, 0) THEN 1 ELSE 0 END AS win, CASE WHEN a.cumsum_amount_follow > NVL(b.max_cumsum_amount_follow, 0) THEN 1 ELSE 0 END AS win_follow, row_number() over( partition by a.FROM_ADDRESS, a.token_address ORDER BY a.BLOCK_TIMESTAMP DESC ) as rn1, row_number() over( partition by a.FROM_ADDRESS, a.token_address ORDER BY a.BLOCK_TIMESTAMP ) as rn2 FROM CumulativeSum_obs a LEFT JOIN MaxCumulativeSum_obs b ON a.FROM_ADDRESS = b.FROM_ADDRESS AND a.token_address = b.token_address AND a.TX_HASH = b.TX_HASH ) order by FROM_ADDRESS, token_address, BLOCK_TIMESTAMP ) a left join tgt_week b on a.date_str = b.date_str ), week_win_info as( SELECT FROM_ADDRESS, sum( case when week_rn = 1 then cumsum_amount else 0 end ) as week1_amount, sum( case when week_rn = 2 then cumsum_amount else 0 end ) as week2_amount, sum( case when week_rn = 3 then cumsum_amount else 0 end ) as week3_amount, sum( case when week_rn = 4 then cumsum_amount else 0 end ) as week4_amount, sum( case when week_rn = 5 then cumsum_amount else 0 end ) as week5_amount, sum( case when week_rn = 6 then cumsum_amount else 0 end ) as week6_amount FROM simple_trade_obs_detail_obs_old_obs WHERE rn1 = 1 group by FROM_ADDRESS ), tgt_from_token_obs as( SELECT DISTINCT FROM_ADDRESS, token_address FROM simple_trade_obs_detail_obs_old_obs WHERE rn2 = 1 AND amount_usd < 0 ), tgt_from_token_obs_time_obs as( SELECT a.*, DATEDIFF(minute, a.first_buy_time, a.first_sell_time) as first_hold_minute, DATEDIFF(minute, b.first_token_buy_time, a.first_buy_time) as first_buy_token_gap_time FROM ( SELECT FROM_ADDRESS, token_address, min( case when amount_usd < 0 then BLOCK_TIMESTAMP else null end ) as first_buy_time, min( case when amount_usd > 0 then BLOCK_TIMESTAMP else null end ) as first_sell_time FROM simple_trade_obs_detail_obs_old_obs GROUP BY FROM_ADDRESS, token_address ) a left join token_info b on a.token_address = b.token_address ), simple_trade_obs_detail_obs as( SELECT a.*, c.first_hold_minute as first_hold_minute, c.first_buy_token_gap_time as first_buy_token_gap_time FROM simple_trade_obs_detail_obs_old_obs a inner join tgt_from_token_obs b on a.FROM_ADDRESS = b.FROM_ADDRESS AND a.token_address = b.token_address left join tgt_from_token_obs_time_obs c on a.FROM_ADDRESS = c.FROM_ADDRESS AND a.token_address = c.token_address ), feat_1_obs as( SELECT FROM_ADDRESS, count(DISTINCT TX_HASH) as hash_cnt, sum(win) as win_cnt, sum(win_follow) as win_follow_cnt, sum( case when (AMOUNT_USD) > 0 then AMOUNT_USD else 0 end ) as all_sell_usd, sum( case when (AMOUNT_USD) < 0 then AMOUNT_USD else 0 end ) as all_buy_usd, sum( case when (amount_usd_follow) > 0 then amount_usd_follow else 0 end ) as all_sell_usd_follow, sum( case when (amount_usd_follow) < 0 then amount_usd_follow else 0 end ) as all_buy_usd_follow, count( DISTINCT case when AMOUNT_USD > 0 then TX_HASH else null end ) as all_sell_cnt, count( DISTINCT case when AMOUNT_USD < 0 then TX_HASH else null end ) as all_buy_cnt, count( DISTINCT case when AMOUNT_USD > 0 then token_address else null end ) as all_sell_token_cnt, count( DISTINCT case when AMOUNT_USD < 0 then token_address else null end ) as all_buy_token_cnt, count(DISTINCT date(BLOCK_TIMESTAMP)) as date_cnt, avg( case when AMOUNT_USD < 0 then abs(AMOUNT_USD) else null end ) as avg_buy_amount, avg( case when AMOUNT_USD > 0 then abs(AMOUNT_USD) else null end ) as avg_sell_amount, - median( case when AMOUNT_USD < 0 then abs(AMOUNT_USD) else null end ) as median_buy_amount, median( case when AMOUNT_USD > 0 then abs(AMOUNT_USD) else null end ) as median_sell_amount, median( case when first_hold_minute > 0 then first_hold_minute else null end ) as median_hold_minute, median( case when first_buy_token_gap_time >= 0 then first_buy_token_gap_time else null end ) as median_first_buy_token_gap_time, avg( case when first_hold_minute < 0 then first_hold_minute else null end ) as avg_hold_minute_minus, sum( case when rn1 = 1 AND cumsum_amount > 0 then cumsum_amount_buy else 0 end ) as win_buy_amount, sum( case when rn1 = 1 AND cumsum_amount < 0 then cumsum_amount_buy else 0 end ) as fail_buy_amount, sum( case when rn1 = 1 AND cumsum_amount > 0 then cumsum_amount else 0 end ) as win_profit_amount, sum( case when rn1 = 1 AND cumsum_amount < 0 then cumsum_amount else 0 end ) as fail_profit_amount, max( case when rn1 = 1 AND cumsum_amount > 0 then cumsum_amount else 0 end ) as win_profit_amount_max, max( case when rn1 = 1 AND cumsum_amount < 0 then cumsum_amount else 0 end ) as fail_profit_amount_max, min( case when rn1 = 1 AND cumsum_amount > 0 then cumsum_amount else 0 end ) as win_profit_amount_min, min( case when rn1 = 1 AND cumsum_amount < 0 then cumsum_amount else 0 end ) as fail_profit_amount_min, max(date(BLOCK_TIMESTAMP)) as max_date, min(date(BLOCK_TIMESTAMP)) as min_date from simple_trade_obs_detail_obs group by FROM_ADDRESS ), feat_2_obs as( SELECT FROM_ADDRESS, COUNT( DISTINCT case when CUMSUM_AMOUNT > 0 then token_address else null end ) as win_token_cnt, COUNT( DISTINCT case when CUMSUM_AMOUNT < 0 then token_address else null end ) as fail_token_cnt, COUNT(DISTINCT token_address) as token_cnt, sum( case when CUMSUM_AMOUNT < 0 then CUMSUM_AMOUNT else 0 end ) as loss_amount, sum( case when CUMSUM_AMOUNT > 0 then CUMSUM_AMOUNT else 0 end ) as profit_amount, COUNT( DISTINCT case when CUMSUM_AMOUNT > 0 then date(BLOCK_TIMESTAMP) else null end ) as win_date_cnt, COUNT( DISTINCT case when CUMSUM_AMOUNT = 0 then token_address else null end ) as buy_zero_token_cnt FROM simple_trade_obs_detail_obs WHERE rn1 = 1 GROUP BY FROM_ADDRESS ), feat_final_agent as( SELECT *, case when fail_profit_ratio = 0 THEN win_profit_ratio else win_profit_ratio /(fail_profit_ratio) end as win_fail_ratio, all_sell_usd + all_buy_usd - TX_FEE_AMOUNT as total_profit, all_sell_usd_follow + all_buy_usd_follow - TX_FEE_AMOUNT as total_profit_follow, WIN_TOKEN_CNT / token_cnt as win_token_ratio, win_date_cnt / date_cnt as win_date_ratio, TX_FEE_AMOUNT / (all_sell_usd - all_buy_usd) as GAS_PROFIT_RATIO from ( SELECT *, case when WIN_CNT = 0 then 0 else WIN_CNT / HASH_CNT end as win_ratio, case when WIN_FOLLOW_CNT = 0 then 0 else WIN_FOLLOW_CNT / HASH_CNT end as follow_win_ratio, case when WIN_PROFIT_AMOUNT = 0 AND WIN_BUY_AMOUNT = 0 THEN 0 ELSE WIN_PROFIT_AMOUNT /(- WIN_BUY_AMOUNT) END as win_profit_ratio, case when FAIL_PROFIT_AMOUNT = 0 AND FAIL_BUY_AMOUNT = 0 THEN 0 ELSE FAIL_PROFIT_AMOUNT / FAIL_BUY_AMOUNT END as fail_profit_ratio FROM ( SELECT a.*, b.win_token_cnt, b.fail_token_cnt, b.token_cnt, b.loss_amount, b.profit_amount, b.win_date_cnt, b.buy_zero_token_cnt, c.TX_FEE_AMOUNT FROM feat_1_obs a left join feat_2_obs b on a.FROM_ADDRESS = b.FROM_ADDRESS left join gas_use_obs c on a.FROM_ADDRESS = c.FROM_ADDRESS ) WHERE HASH_CNT > 0 ) ORDER BY win_fail_ratio DESC, max_date DESC ), feat_final as( SELECT a.*, b.wallet_age, c.week1_amount, c.week2_amount, c.week3_amount, c.week4_amount, d.normal_token_cnt, d.all_token_cnt, d.normal_token_ratio FROM feat_final_agent a left join tgt_addr_obs_age b on a.FROM_ADDRESS = b.address left join week_win_info c on a.FROM_ADDRESS = c.FROM_ADDRESS left join trader_token_info d on a.FROM_ADDRESS = d.FROM_ADDRESS ) SELECT * FROM ( SELECT * FROM feat_final WHERE WIN_DATE_RATIO > 0.6 AND TOTAL_PROFIT > 1000 -- AND GAS_PROFIT_RATIO < 0.5 -- AND MEDIAN_FIRST_BUY_TOKEN_GAP_TIME < 60 AND HASH_CNT > 20 -- AND TOTAL_PROFIT_FOLLOW > 0 AND WIN_TOKEN_RATIO > 0.7 AND NORMAL_TOKEN_RATIO >= 0.7 -- AND TOTAL_PROFIT > (ALL_BUY_USD) -- AND median_buy_amount < 100 AND normal_token_ratio > 0.9 ) ORDER BY MAX_DATE DESC

0xA1337813843dC2A76Ef9E2ABFc1F4d4697E4719B