with base as( SELECT tr.tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, 'ethereum to ' || TO_VARIANT( SUBSTR(ethereum.public.udf_hex_to_int(data:value), -4) ) as pair, AMOUNT_USD as v FROM ethereum.core.fact_traces tr inner join ethereum.core.ez_eth_transfers ez on ez.tx_hash = tr.tx_hash where ez.block_number >= 13964277 and origin_to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and ez.eth_to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and ez.eth_from_Address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT_USD > 0 UNION all SELECT ez.tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, CASE when DECODED_LOG:wad is null then 'ethereum to ' || TO_VARIANT(SUBSTR(DECODED_LOG:value, -4)) when DECODED_LOG:value is null then 'ethereum to ' || TO_VARIANT(SUBSTR(DECODED_LOG:wad, -4)) end as pair, amount_usd as v FROM ethereum.core.ez_token_transfers tr inner join ethereum.core.fact_decoded_event_logs ez on ez.tx_hash = tr.tx_hash WHERE ez.block_number >= 13479383 and tr.contract_address in ( '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48', '0xdac17f958d2ee523a2206206994597c13d831ec7', '0x6b175474e89094c44da98b954eedeac495271d0f' ) and to_Address in ( '0x41d3d33156ae7c62c094aae2995003ae63f587b3', '0xd7aa9ba6caac7b0436c91396f22ca5a7f31664fc', '0x095d2918b03b2e86d68551dcf11302121fb626c9' ) and event_name = 'Transfer' and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT_USD > 0 UNION all SELECT tr.tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, 'Arbitrum to ' || TO_VARIANT(SUBSTR(ETH_VALUE_PRECISE_RAW, -4)) as pair, AMOUNT_USD as v FROM arbitrum.core.fact_traces tr inner join arbitrum.core.ez_eth_transfers ez on ez.tx_hash = tr.tx_hash where ez.block_number >= 4478202 and ez.eth_to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' UNION all SELECT ez.tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, CASE when DECODED_LOG:wad is null then 'Arbitrum to ' || TO_VARIANT(SUBSTR(DECODED_LOG:value, -4)) when DECODED_LOG:value is null then 'Arbitrum to ' || TO_VARIANT(SUBSTR(DECODED_LOG:wad, -4)) end as pair, AMOUNT as v FROM arbitrum.core.ez_token_transfers tr inner join arbitrum.core.fact_decoded_event_logs ez on ez.tx_hash = tr.tx_hash WHERE ez.block_number >= 2494021 and tr.contract_address in ( '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1', '0xaf88d065e77c8cc2239327c5edb3a432268e5831', '0xff970a61a04b1ca14834a43f5de4533ebddb5cc8', '0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' ) and to_Address in ( '0x41d3d33156ae7c62c094aae2995003ae63f587b3', '0xd7aa9ba6caac7b0436c91396f22ca5a7f31664fc', '0x095d2918b03b2e86d68551dcf11302121fb626c9', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' ) and event_name = 'Transfer' and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT > 0 UNION all SELECT tx_hash as h, date_trunc('month', BLOCK_TIMESTAMP) as d, eth_from_Address as ua, 'Optimism to ' || TO_VARIANT(SUBSTR(AMOUNT_PRECISE_RAW, -4)) as pair, AMOUNT_USD as v FROM optimism.core.ez_eth_transfers ez where block_number >= 3674048 and eth_to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and eth_from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT_USD > 0 UNION all SELECT tx_hash as h, date_trunc('month', BLOCK_TIMESTAMP) as d, from_Address as ua, 'Optimism to ' || TO_VARIANT(SUBSTR(RAW_AMOUNT_PRECISE, -4)) as pair, AMOUNT as v FROM optimism.core.ez_token_transfers WHERE block_number >= 3674048 and contract_address in ( '0x7f5c764cbc14f9669b88837ca1490cca17c31607', '0x94b008aa00579c1307b0ef2c499ad98a8ce58e58', '0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' ) and to_Address in ( '0x41d3d33156ae7c62c094aae2995003ae63f587b3', '0xd7aa9ba6caac7b0436c91396f22ca5a7f31664fc', '0x095d2918b03b2e86d68551dcf11302121fb626c9', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' ) and from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT > 0 UNION all SELECT tr.tx_hash as h, date_trunc('month', tr.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, 'Base to ' || TO_VARIANT(SUBSTR(ETH_VALUE_PRECISE_RAW, -4)) as pair, ETH_VaLUE * PRice as v FROM base.core.fact_traces tr inner join crosschain.price.fact_hourly_token_prices pr on pr.hour = date_trunc('hour', tr.block_timestamp) where tr.block_number >= 1980361 and tr.to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and pr.token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' and pr.provider = 'coinmarketcap' and pr.blockchain = 'ethereum' and ETH_VaLUE > 0 UNION all SELECT ez.tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, tr.from_Address as ua, CASE when DECODED_LOG:wad is null then 'Polygon to ' || TO_VARIANT(SUBSTR(DECODED_LOG:value, -4)) when DECODED_LOG:value is null then 'Polygon to ' || TO_VARIANT(SUBSTR(DECODED_LOG:wad, -4)) end as pair, AMOUNT_USD as v FROM polygon.core.ez_token_transfers tr inner join polygon.core.fact_decoded_event_logs ez on ez.tx_hash = tr.tx_hash WHERE ez.block_number >= 22307105 and tr.contract_address in ( '0x7ceb23fd6bc0add59e62ac25578270cff1b9f619', '0x8f3Cf7ad23Cd3CaDbD9735AFf958023239c6A063', '0xc2132d05d31c914a87c6611c10748aeb04b58e8f', '0x2791bca1f2de4661ed88a30c99a7a9449aa84174' ) and to_Address in ( '0x41d3d33156ae7c62c094aae2995003ae63f587b3', '0xd7aa9ba6caac7b0436c91396f22ca5a7f31664fc', '0x095d2918b03b2e86d68551dcf11302121fb626c9', '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc', '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and event_name = 'Transfer' and tr.from_address != '0xd9d74a29307cc6fc8bf424ee4217f1a587fbc8dc' and AMOUNT_USD > 0 UNION all SELECT tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, BNB_FROM_ADDRESS as ua, 'BSC to ' || TO_VARIANT(SUBSTR(AMOUNT_PRECISE_RAW, -4)) as pair, AMOUNT_USD as v FROM bsc.core.ez_bnb_transfers ez where ez.block_number >= 14724341 and origin_to_address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and BNB_TO_ADDRESS in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and AMOUNT_USD > 0 UNION all SELECT tx_hash as h, date_trunc('month', ez.BLOCK_TIMESTAMP) as d, from_Address as ua, 'BSC to ' || TO_VARIANT(SUBSTR (RAW_AMOUNT_PRECISE, -4)) as pair, amount_usd as v FROM bsc.core.ez_token_transfers ez WHERE ez.block_number >= 14723809 and contract_address = '0x2170ed0880ac9a755fd29b2688956bd959f933f8' and to_Address in ( '0x80c67432656d59144ceff962e8faf8926599bcf8', '0xe4edb277e41dc89ab076a1f049f4a3efa700bce8', '0xee73323912a4e3772b74ed0ca1595a152b0ef282' ) and AMOUNT_USD > 0 ), all_users as( SELECT ua, sum(v) as Volume, count(h) as transaction_count, count(DISTINCT d) as month, count(DISTINCT pair) as pair_chain_count from base WHERE ( pair LIKE '%9001%' or pair LIKE '%9002%' OR pair LIKE '%9003%' OR pair LIKE '%9004%' OR pair LIKE '%9005%' OR pair LIKE '%9006%' OR pair LIKE '%9007%' OR pair LIKE '%9008%' OR pair LIKE '%9009%' OR pair LIKE '%9010%' OR pair LIKE '%9011%' OR pair LIKE '%9012%' OR pair LIKE '%9013%' OR pair LIKE '%9014%' OR pair LIKE '%9015%' OR pair LIKE '%9016%' OR pair LIKE '%9017%' OR pair LIKE '%9021%' OR pair LIKE '%9023%' OR pair LIKE '%9024%' OR pair LIKE '%9030%' ) GROUP BY 1 HAVING sum(v) >= 200 and count(h) >= 10 ), users_score as ( SELECT ua, CASE when transaction_count < 10 then 0 when transaction_count >= 10 and transaction_count < 25 then 1 when transaction_count >= 25 and transaction_count < 50 then 2 when transaction_count >= 50 then 3 end as tr_s, case when Volume < 1000 then 0 when Volume >= 1000 and Volume < 2500 then 1 when Volume >= 2500 and Volume < 5000 then 2 when Volume >= 5000 then 3 end as v_s, case when month < 3 then 0 when month >= 3 and month < 6 then 1 when month >= 6 and month < 9 then 2 when month >= 9 and month < 12 then 3 when month >= 12 then 4 end as m_s, case when pair_chain_count < 6 then 0 when pair_chain_count >= 6 and pair_chain_count < 10 then 1 when pair_chain_count >= 10 then 2 end as p_s from all_users WHERE volume > 200 and transaction_count > 10 ) SELECT u_a.ua as "user address", p_s + m_s + v_S + tr_s as Score, transaction_count, Volume, month, pair_chain_count FROM all_users u_a inner join users_score u_s on u_s.ua = u_a.ua ORDER BY score desc

orbiter Airdrop simulator(based on connext)