-- forked from rezarwz / LI.FI user Rank simulator based on connext airdrop @ https://flipsidecrypto.xyz/rezarwz/q/RcUx57DavzsX/li.fi-user-rank-simulator-based-on-connext-airdrop with all_tx as ( SELECT 'arbitrum' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, case when amount_usd is null and SYMBOL = 'DAI' then amount when amount_usd is null and SYMBOL = 'axlUSDC' then amount when amount_usd is null and SYMBOL = 'aArbUSDT' then amount when amount_usd is null and SYMBOL = 'aArbUSDC' then amount when amount_usd is null and SYMBOL = 'USD+' then amount when amount_usd is null and SYMBOL = 'USDC' then amount when amount_usd is null and SYMBOL = 'USDCx' then amount when amount_usd is null and SYMBOL = 'USDT' then amount else amount_usd end as amount_usd from arbitrum.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 32495475 UNION all SELECT 'arbitrum' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, eth_FROM_ADDRESS as ua, AMOUNT_USD from arbitrum.core.ez_eth_transfers where origin_from_address = eth_from_address and ETH_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 32784976 UNION all SELECT 'avalanche' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, case when amount_usd is null and SYMBOL = 'USDT.e' then amount when amount_usd is null and SYMBOL = 'USDT' then amount when amount_usd is null and SYMBOL = 'USDt' then amount when amount_usd is null and SYMBOL = 'USDC.e' then amount when amount_usd is null and SYMBOL = 'USDC+' then amount when amount_usd is null and SYMBOL = 'USD+' then amount when amount_usd is null and SYMBOL = 'BUSD' then amount when amount_usd is null and SYMBOL = 'avUSDC' then amount when amount_usd is null and SYMBOL = 'aAvaUSDT' then amount when amount_usd is null and SYMBOL = 'aAvaUSDC' then amount else amount_usd end as amount_usd from avalanche.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 21611065 UNION all SELECT 'avalanche' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, AVAX_FROM_ADDRESS as ua, AMOUNT_USD from avalanche.core.ez_avax_transfers where origin_from_address = AVAX_FROM_ADDRESS and AVAX_to_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 21611479 UNION all SELECT 'optimism' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, case when amount_usd is null and SYMBOL = 'DAI' then amount when amount_usd is null and SYMBOL = 'USDT' then amount when amount_usd is null and SYMBOL = 'USDC' then amount when amount_usd is null and SYMBOL = 'USDCx' then amount else amount_usd end as amount_usd from optimism.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 31525423 UNION all SELECT 'optimism' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, eth_FROM_ADDRESS as ua, AMOUNT_USD from optimism.core.ez_eth_transfers where origin_from_address = eth_from_address and ETH_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 33141404 UNION all SELECT 'ethereum' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, amount_usd from ethereum.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 15799899 UNION all SELECT 'ethereum' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, eth_FROM_ADDRESS as ua, AMOUNT_USD from ethereum.core.ez_eth_transfers where origin_from_address = eth_from_address and ETH_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 15824186 UNION all SELECT 'bsc' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, case when amount_usd is null and SYMBOL = 'DAI' then amount when amount_usd is null and SYMBOL = 'USDT' then amount when amount_usd is null and SYMBOL = 'USDC' then amount when amount_usd is null and SYMBOL = 'BUSD' then amount else amount_usd end as amount_usd from bsc.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 22539051 UNION all SELECT 'bsc' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, bnb_FROM_ADDRESS as ua, AMOUNT_USD from bsc.core.ez_bnb_transfers where origin_from_address = bnb_from_address and BNB_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 22569955 UNION all SELECT 'polygon' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, FROM_ADDRESS as ua, case when amount_usd is null and SYMBOL = 'USDTx' then amount when amount_usd is null and SYMBOL = 'USDT' then amount when amount_usd is null and SYMBOL = 'USDCx' then amount when amount_usd is null and SYMBOL = 'USDC' then amount when amount_usd is null and SYMBOL = 'DAIx' then amount when amount_usd is null and SYMBOL = 'DAI' then amount when amount_usd is null and SYMBOL = 'BUSD' then amount when amount_usd is null and SYMBOL = 'aPolUSDT' then amount when amount_usd is null and SYMBOL = 'aPolUSDC' then amount else amount_usd end as amount_usd from polygon.core.ez_token_transfers where ORIGIN_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and to_address = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and from_address = origin_from_address and block_number >= 34542264 UNION all SELECT 'polygon' as blockchain, TX_HASH, date_trunc('month', block_timestamp) as month, matic_FROM_ADDRESS as ua, AMOUNT_USD from polygon.core.ez_matic_transfers where origin_from_address = matic_from_address and matic_TO_ADDRESS = '0x1231deb6f5749ef6ce6943a275a1d3e7486f4eae' and block_number >= 34542264 ), all_users as( SELECT ua, count(DISTINCT month) as ds_month, count(DISTINCT blockchain) as ds_blck, count(TX_HASH) as tr_count, sum(AMOUNT_USD) as volume FROM all_tx GROUP BY 1 ), users_score as ( SELECT ua, CASE when tr_count < 10 then 0 when tr_count >= 10 and tr_count < 25 then 1 when tr_count >= 25 and tr_count < 50 then 2 when tr_count >= 50 then 3 end as tr_score, 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 volume_score, case when ds_month < 3 then 0 when ds_month >= 3 and ds_month < 6 then 1 when ds_month >= 6 and ds_month < 9 then 2 when ds_month >= 9 and ds_month < 12 then 3 when ds_month >= 12 then 4 end as month_score, case when DS_BLCK < 3 then 0 when DS_BLCK >= 3 and DS_BLCK < 6 then 1 when DS_BLCK = 6 then 2 end as src_chain_score from all_users WHERE volume > 100 and tr_count > 5 ) SELECT u_a.ua as address, ENS_NAME, CASE when src_chain_score + month_score + volume_score + tr_score = 0 then 1 else src_chain_score + month_score + volume_score + tr_score end as total_point, ROW_NUMBER() over( order by src_chain_score + month_score + volume_score + tr_score desc ) as rank, tr_count as transaction_count, volume, ds_month as month, ds_blck as blockchains FROM all_users u_a inner join users_score u_s on u_s.ua = u_a.ua left join crosschain.ens.ez_ens_domains en on en.OWNER = u_a.ua WHERE ENS_NAME!='227723' ORDER BY rank asc

Mehdi_Mahan-1779