with all_arb as (SELECT BLOCK_TIMESTAMP::date as date ,SYMBOL as token, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from arbitrum.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0x47e7ef24' and ORIGIN_TO_ADDRESS = '0xc6ade8a68026d582ab37b879d188caf7e405dd09' and FROM_ADDRESS != '0x0000000000000000000000000000000000000000'), all_arb_remov as (SELECT BLOCK_TIMESTAMP::date as date2 ,SYMBOL as token2, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from arbitrum.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0xf3fef3a3' and FROM_ADDRESS = '0xc6ade8a68026d582ab37b879d188caf7e405dd09' and To_ADDRESS != '0x0000000000000000000000000000000000000000'), all_eth as (SELECT BLOCK_TIMESTAMP::date as date ,SYMBOL as token, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from ethereum.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0x47e7ef24' and ORIGIN_TO_ADDRESS = '0xc6ade8a68026d582ab37b879d188caf7e405dd09' and FROM_ADDRESS != '0x0000000000000000000000000000000000000000'), all_eth_remov as (SELECT BLOCK_TIMESTAMP::date as date2 ,SYMBOL as token2, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from ethereum.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0xf3fef3a3' and FROM_ADDRESS = '0xc6ade8a68026d582ab37b879d188caf7e405dd09' and To_ADDRESS != '0x0000000000000000000000000000000000000000'), all_bnb as (SELECT BLOCK_TIMESTAMP::date as date ,SYMBOL as token, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from bsc.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0x47e7ef24' and ORIGIN_TO_ADDRESS = '0xd00789260984160a64dcf19a03896dff73bf4514' and FROM_ADDRESS != '0x0000000000000000000000000000000000000000'), all_bnb_remov as (SELECT BLOCK_TIMESTAMP::date as date2 ,SYMBOL as token2, AMOUNT, AMOUNT_USD, tx_hash, CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS from bsc.core.ez_token_transfers where BLOCK_TIMESTAMP::date >= '2024-01-01' and ORIGIN_FUNCTION_SIGNATURE = '0xf3fef3a3' and FROM_ADDRESS = '0xd00789260984160a64dcf19a03896dff73bf4514' and to_ADDRESS != '0x0000000000000000000000000000000000000000'), inflowa as (SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT_USD as usd_volume , date, tx_hash from all_arb where date > '2024-03-15 00:00:00.000' union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT_USD as usd_volume , date, tx_hash from all_arb where date <= '2024-03-15 00:00:00.000' and TOKEN != 'ezETH' union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT*avg(price) as usd_volume , date, tx_hash from all_arb join crosschain.price.fact_hourly_token_prices on date=HOUR::date where date <= '2024-03-15 00:00:00.000' and PROVIDER = 'coingecko' and TOKEN_ADDRESS = '0xbf5495efe5db9ce00f80364c8b423567e58d2110' and TOKEN = 'ezETH' GROUP by all union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT_USD as usd_volume , date, tx_hash from all_eth where date < '2024-03-19 00:00:00.000' and TOKEN != 'sUSDe' union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT as usd_volume , date, tx_hash from all_eth where date < '2024-03-19 00:00:00.000' and TOKEN = 'sUSDe' union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT_USD as usd_volume , date, tx_hash from all_eth join crosschain.price.fact_hourly_token_prices on date=HOUR::date where date >= '2024-03-19 00:00:00.000' union SELECT ORIGIN_FROM_ADDRESS, token, AMOUNT as volume, AMOUNT_USD as usd_volume , date, tx_hash from all_bnb ), outflowa as ( SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT_USD as usd_volume2 , date2, tx_hash from all_arb_remov where date2 > '2024-03-15 00:00:00.000' union SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT_USD as usd_volume2 , date2, tx_hash from all_arb_remov where date2 <= '2024-03-15 00:00:00.000' and TOKEN2 != 'ezETH' union SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT*avg(price) as usd_volume2 , date2, tx_hash from all_arb_remov join crosschain.price.fact_hourly_token_prices on date2=HOUR::date where date2 <= '2024-03-15 00:00:00.000' and PROVIDER = 'coingecko' and TOKEN_ADDRESS = '0xbf5495efe5db9ce00f80364c8b423567e58d2110' and TOKEN2 = 'ezETH' GROUP by all union SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT_USD as usd_volume2 , date2, tx_hash from all_bnb_remov UNION SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT_USD as usd_volume2 , date2, tx_hash from all_eth_remov where date2 < '2024-03-19 00:00:00.000' and TOKEN2 != 'sUSDe' union SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT as usd_volume2 , date2, tx_hash from all_eth_remov where date2 < '2024-03-19 00:00:00.000' and TOKEN2 = 'sUSDe' union SELECT ORIGIN_FROM_ADDRESS as add2, token2, AMOUNT as volume, AMOUNT_USD as usd_volume2 , date2, tx_hash from all_eth_remov join crosschain.price.fact_hourly_token_prices on date2=HOUR::date where date2 >= '2024-03-19 00:00:00.000'), inflow_fin as (SELECT DISTINCT ORIGIN_FROM_ADDRESS, sum(usd_volume) as usd_volume from inflowa GROUP by all), outflow_fin as (SELECT DISTINCT add2, sum(usd_volume2) as usd_volume2 from outflowa GROUP by all), final_user as ( SELECT COALESCE(inflow_fin.ORIGIN_FROM_ADDRESS , outflow_fin.add2) as user, COALESCE(-1 * outflow_fin.usd_volume2, 0) as "Outflows", COALESCE(inflow_fin.usd_volume, 0) as "Inflows", COALESCE(inflow_fin.usd_volume, 0) - COALESCE(outflow_fin.usd_volume2, 0) as "Net volume" FROM inflow_fin fULL JOIN outflow_fin ON inflow_fin.ORIGIN_FROM_ADDRESS = outflow_fin.add2) SELECT count(DISTINCT user ) as users , case when "Net volume" < 100 then 'A. Less than $100' when "Net volume" > 100 and "Net volume" < 501 then 'B. Between $100~$500' when "Net volume" > 500 and "Net volume" < 1001 then 'C. Between $501~$1001' when "Net volume" > 1000 and "Net volume" < 5001 then 'D. Between $1001~$5000' when "Net volume" > 5000 then 'E. More than $5000' end as "Volume level" from final_user group by all having "Volume level" is not null ORDER by 2

Blackwing.fi