with table1 as (with arbitrum as (select to_address, sum(raw_amount)/pow(10,18) as "Airdrop Volume" from arbitrum.core.fact_token_transfers where contract_address='0x912ce59144191c1204e64559fe8253a0e49e6548' and from_address='0x67a24ce4321ab3af51c2d0a4801c3e111d88c9d9' and ORIGIN_FUNCTION_SIGNATURE in ('0x4e71d92d','0x78e2b594') group by 1 order by 2 desc), --------------------------------------------------------------------------- starknet as (with eth as (select block_timestamp, tx_hash, origin_from_address, origin_to_address, amount_usd, 'ETH' as symbol, case when (ORIGIN_FUNCTION_SIGNATURE='0xe2bbb158' or ORIGIN_FUNCTION_SIGNATURE='0xb6b55f25') then 'Deposit' when (ORIGIN_FUNCTION_SIGNATURE='0x00f714ce' or ORIGIN_FUNCTION_SIGNATURE='0x2e1a7d4d') then 'Withdraw' end as action from ethereum.core.ez_eth_transfers where block_timestamp::date>='2023-03-16' and (IDENTIFIER='CALL_ORIGIN' or IDENTIFIER='CALL_0_1') and (ORIGIN_FUNCTION_SIGNATURE='0xe2bbb158' or ORIGIN_FUNCTION_SIGNATURE='0xb6b55f25' -- deposit or ORIGIN_FUNCTION_SIGNATURE='0x00f714ce' or ORIGIN_FUNCTION_SIGNATURE='0x2e1a7d4d') -- withdraw and origin_to_address='0xae0ee0a63a2ce6baeeffe56e7714fb4efe48d419' -- StarkNet: StarkGate ETH Bridge order by 1), token as (select block_timestamp, tx_hash, origin_from_address, origin_to_address, amount_usd, symbol, case when (ORIGIN_FUNCTION_SIGNATURE='0xe2bbb158' or ORIGIN_FUNCTION_SIGNATURE='0xb6b55f25') then 'Deposit' when (ORIGIN_FUNCTION_SIGNATURE='0x00f714ce' or ORIGIN_FUNCTION_SIGNATURE='0x2e1a7d4d') then 'Withdraw' end as action from ethereum.core.ez_token_transfers where block_timestamp::date>='2023-03-16' and (ORIGIN_FUNCTION_SIGNATURE='0xe2bbb158' or ORIGIN_FUNCTION_SIGNATURE='0xb6b55f25' -- deposit or ORIGIN_FUNCTION_SIGNATURE='0x00f714ce' or ORIGIN_FUNCTION_SIGNATURE='0x2e1a7d4d') -- withdraw and (origin_to_address='0x659a00c33263d9254fed382de81349426c795bb6' -- StarkNet: StarkGate DAI Bridge or origin_to_address='0x283751a21eafbfcd52297820d27c1f1963d9b5b4' -- StarkNet: StarkGate WBTC Bridge or origin_to_address='0xbb3400f107804dfb482565ff1ec8d8ae66747605' -- StarkNet: StarkGate USDT Bridge or origin_to_address='0xf6080d9fbeebcd44d89affbfd42f098cbff92816') -- StarkNet: StarkGate USDC Bridge order by 1) select * from eth union all select * from token) select to_address, sum(amount_usd) as "Total Deposit Volume", case when sum(amount_usd)<=1 then 'V<=1 $USD' when sum(amount_usd)>1 and sum(amount_usd)<=10 then '110 and sum(amount_usd)<=100 then '10100 and sum(amount_usd)<=1000 then '1001000 and sum(amount_usd)<=10000 then '1K10000 and sum(amount_usd)<=100000 then '10K100000 then 'V>100K $USD' end as "Group" from arbitrum left join starknet on arbitrum.to_address=starknet.origin_from_address where block_timestamp::date>='2023-03-16' and amount_usd>0 group by 1 order by 3 DESC) select "Group", count(distinct to_address) as "User Count" from table1 group by 1 order by 2

Impact of Arbitrum Airdrop on Starknet