with ss1 as (select to_address as address, sum(amount) as amt_edited from ethereum.core.ez_token_transfers where from_address = lower('0x93fff4028927f53f708534397ed349b9cd4e2f9f') and contract_address = '0xfe0c30065b384f05761f15d0cc899d4f9f9cc0eb' group by 1 ), deposit_amt as ( select to_address, sum(amount) as total_deposit_amt from ethereum.core.ez_token_transfers where (contract_address = lower('0x35fA164735182de50811E8e2E824cFb9B6118ac2') or contract_address = lower('0xCd5fE23C85820F7B72D0926FC9b05b43E359b7ee')) and to_address in (select address from ss1) and date(block_timestamp) <= '2024-03-18' group by 1 union all select to_address, sum(amount) as total_deposit_amt from arbitrum.core.ez_token_transfers where contract_address = lower('0x35751007a407ca6FEFfE80b3cB397736D2cf4dbe') and to_address in (select address from ss1) and date(block_timestamp) <= '2024-03-18' group by 1 union all select to_address, sum(amount) as total_deposit_amt from base.core.ez_token_transfers where contract_address = lower('0x04C0599Ae5A44757c0af6F9eC3b93da8976c150A') and to_address in (select address from ss1) and date(block_timestamp) <= '2024-03-18' group by 1 union all select to_address, sum(amount) as total_deposit_amt from blast.core.ez_token_transfers where contract_address = lower('0x04C0599Ae5A44757c0af6F9eC3b93da8976c150A') and to_address in (select address from ss1) and date(block_timestamp) <= '2024-03-18' group by 1 ) select label, count(distinct address) as add_cnt, (sum(deposit_amt)/count(distinct address))/10 as avg_deposit_per_wallet, sum(deposit_amt) as avg_amt from (select a.address, a.amt_edited as reward, case when a.amt_edited > 0 and a.amt_edited < 200 then '<200 ETHFI' when a.amt_edited >= 200 and a.amt_edited < 400 then '200-400 ETHFI' when a.amt_edited >= 400 and a.amt_edited < 600 then '400-600 ETHFI' when a.amt_edited >= 600 and a.amt_edited < 800 then '600-800 ETHFI' when a.amt_edited >= 800 and a.amt_edited < 1000 then '800-1000 ETHFI' when a.amt_edited >= 1000 and a.amt_edited < 5000 then '1000-5000 ETHFI' when a.amt_edited >= 5000 and a.amt_edited < 10000 then '5000-9999 ETHFI' when a.amt_edited = 10000 then '10,000 ETHFI' else '>10,000 ETHFI' end as label, b.total_deposit_amt as deposit_amt from ss1 a join deposit_amt b on a.address = b.to_address) group by 1

Etherfi Airdrop Analysis