with tgt_addr as( SELECT DISTINCT FROM_ADDRESS as address FROM ethereum.core.fact_transactions where date(BLOCK_TIMESTAMP) >= date('2022-09-15') AND date(BLOCK_TIMESTAMP) <= date('2023-09-08') and NONCE=0 ), coin_msg 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) >= date('2022-09-15') AND date(BLOCK_TIMESTAMP) <= date('2023-09-08') 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) >= date('2022-09-15') AND date(BLOCK_TIMESTAMP) <= date('2023-09-08') and ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' group by TOKEN_OUT )b on a.TOKEN=b.TOKEN ), token_detail as( SELECT TX_HASH ,TOKEN_IN ,(case when TOKEN_IN='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then AMOUNT_IN*1900 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*1900 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 ,AMOUNT_OUT ,SYMBOL_OUT ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= date('2022-09-15') AND date(BLOCK_TIMESTAMP) <= date('2023-09-08') and ( TOKEN_IN in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) OR TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) ) and ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' ), trade_msg_old as( SELECT a.* FROM ( SELECT a.* ,b.FROM_ADDRESS FROM ( SELECT TX_HASH ,TOKEN_IN ,(case when TOKEN_IN='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then AMOUNT_IN*1900 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*1900 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 ,AMOUNT_OUT ,SYMBOL_OUT ,CONTRACT_ADDRESS ,BLOCK_TIMESTAMP FROM ethereum.defi.ez_dex_swaps where date(BLOCK_TIMESTAMP) >= date('2022-09-15') AND date(BLOCK_TIMESTAMP) <= date('2023-09-08') and ( TOKEN_IN in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) OR TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) ) and ORIGIN_TO_ADDRESS='0x7a250d5630b4cf539739df2c5dacb4c659f2488d' )a left join ethereum.core.fact_transactions b on a.TX_HASH=b.TX_HASH )a inner join tgt_addr b on a.FROM_ADDRESS=b.address ), -- top1k_token as( -- SELECT -- BUY_TOKEN -- ,sum(AMOUNT_BUY_USD) as AMOUNT_BUY_USD -- ,count(distinct date(BLOCK_TIMESTAMP)) as date_cnt -- ,COUNT(DISTINCT TX_HASH) as hash_cnt -- FROM -- token_detail where BUY_TOKEN is not null -- group by BUY_TOKEN HAVING date_cnt > 150 ORDER BY AMOUNT_BUY_USD DESC LIMIT 1000 -- ), txn_transfer_out 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 ,RAW_AMOUNT ,(case when CONTRACT_ADDRESS='0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then RAW_AMOUNT/(1e18)*1900 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' ) )a inner join tgt_addr b on a.TO_ADDRESS=b.address GROUP BY TX_HASH,TO_ADDRESS ), trade_msg as( SELECT a.* ,b.AMOUNT_SELL_USD1 ,b.min_token_address ,b.max_token_address FROM trade_msg_old a left join txn_transfer_out b on a.FROM_ADDRESS=b.TO_ADDRESS and a.TX_HASH=b.TX_HASH and a.TOKEN_OUT in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) ), tgt_addr_filter as( SELECT * ,case when malicious_coin_cnt > 10 then 0 when malicious_coin_cnt > 5 and malicious_coin_cnt <= 10 then 1 when malicious_coin_cnt > 1 and malicious_coin_cnt <= 5 then 2 else 3 end as malicious_score ,case when win_token_cnt <=5 then 0 when win_token_cnt > 5 and win_token_cnt <= 10 then 1 when win_token_cnt > 10 and win_token_cnt <= 20 then 2 else 3 end as win_token_cnt_score ,case when win_token_ratio <=0.5 then 0 when win_token_ratio > 0.5 and win_token_ratio <= 0.6 then 1 when win_token_ratio > 0.6 and win_token_ratio <= 0.7 then 2 when win_token_ratio > 0.7 and win_token_ratio <= 0.8 then 3 when win_token_ratio > 0.8 and win_token_ratio <= 0.9 then 4 when win_token_ratio > 0.9 then 4 else 3 end as win_token_ratio_score ,case when profit <=10000 then 1 when profit > 10000 and profit <= 20000 then 2 when profit > 20000 and profit <= 30000 then 3 when profit > 30000 then 4 else 0 end as profit_score FROM ( SELECT FROM_ADDRESS ,COUNT(DISTINCT (case when is_malicious_coin=1 then TOKEN else null end)) as malicious_coin_cnt ,COUNT(DISTINCT(case when profit > 0 then TOKEN else null end)) as win_token_cnt ,COUNT(DISTINCT TOKEN) as token_cnt ,avg(hold_minute) as avg_hold_minute ,sum(profit) as profit ,median(buy_amount_usd) as median_buy_amount_usd ,sum(case when profit > 0 then buy_amount_usd else 0 end) as win_buy_amount ,sum(case when profit > 0 then profit else 0 end) as win_profit ,sum(case when profit <= 0 then buy_amount_usd else 0 end) as loss_buy_amount ,sum(case when profit <= 0 then profit else 0 end) as loss_profit ,COUNT(DISTINCT(case when profit > 0 then TOKEN else null end)) / COUNT(DISTINCT TOKEN) as win_token_ratio ,sum(win_cnt) as win_cnt ,sum(fail_cnt) as fail_cnt ,sum(win_cnt) / (sum(fail_cnt) + sum(win_cnt)) as win_cnt_ratio ,max(max_date_buyorsell) as max_date_buyorsell ,date(max(max_date_buyorsell)) as max_date ,date(min(min_date_buyorsell)) as min_date FROM ( SELECT a.* ,b.is_malicious_coin ,case when a.profit > 0 then a.txn_cnt else null end as win_cnt ,case when a.profit < 0 then a.txn_cnt else null end as fail_cnt FROM ( select * from ( SELECT FROM_ADDRESS ,TOKEN ,sum(profit) as profit ,max(max_date_buyorsell) as max_date_buyorsell ,max(min_date_buyorsell) as min_date_buyorsell ,case when COUNT(DISTINCT action_type)=2 then 1 else 0 end as is_normal_action ,sum(txn_cnt) as txn_cnt ,sum(case when action_type='buy' then profit else 0 end) as buy_amount_usd ,sum(case when action_type='buy' then txn_cnt else 0 end) as buy_cnt ,DATEDIFF(minute, min(min_date_buyorsell), max(max_date_buyorsell)) as hold_minute FROM ( SELECT FROM_ADDRESS ,BUY_TOKEN as TOKEN ,-sum(AMOUNT_BUY_USD) profit ,max(BLOCK_TIMESTAMP) as max_date_buyorsell ,min(BLOCK_TIMESTAMP) as min_date_buyorsell ,'buy' as action_type ,count(distinct(TX_HASH) ) as txn_cnt FROM trade_msg where BUY_TOKEN is not null group by FROM_ADDRESS,BUY_TOKEN UNION ALL SELECT FROM_ADDRESS ,SELL_TOKEN as TOKEN -- ,sum(AMOUNT_SELL_USD) profit ,sum( case when AMOUNT_SELL_USD1 is not null then AMOUNT_SELL_USD1 else AMOUNT_SELL_USD end ) as profit ,max(BLOCK_TIMESTAMP) as max_date_buyorsell ,min(BLOCK_TIMESTAMP) as min_date_buyorsell ,'sell' as action_type ,count(distinct(TX_HASH) ) as txn_cnt FROM trade_msg where SELL_TOKEN is not null group by FROM_ADDRESS,SELL_TOKEN )group by FROM_ADDRESS,TOKEN HAVING not (COUNT(DISTINCT action_type)=1 and max(action_type)='sell') ) )a left join coin_msg b on a.TOKEN=b.TOKEN )where token not in ( '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ,'0xdac17f958d2ee523a2206206994597c13d831ec7' ,'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' ) GROUP BY FROM_ADDRESS )where win_token_cnt > 5 and profit > 10000 and win_token_ratio > 0.5 -- and malicious_coin_cnt=0 ORDER BY max_date_buyorsell DESC ) SELECT * ,(malicious_score + win_token_cnt_score + win_token_ratio_score + profit_score) as score FROM tgt_addr_filter

Query	Creator	Created
uni uniswap kyberswap hashflow maverick dodo	5041e2fe-4e4a-4483-973f-64d233b94bf3	Sep 6, 2023
2023-07-12 04:10 PM uniswap kyberswap hashflow maverick dodo	5041e2fe-4e4a-4483-973f-64d233b94bf3	Jul 12, 2023

5041e2fe-4e4a-4483-973f-64d233b94bf3