with MainAcrossTable as ( select block_timestamp, tx_hash, regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') as Segmented_Data, ethereum.public.udf_hex_to_int(Segmented_Data[0]) / pow(10,18) as amount, ethereum.public.udf_hex_to_int(Segmented_Data[1]) as originChainId, ethereum.public.udf_hex_to_int(Segmented_Data[2]) as destinationChainId, origin_from_address, origin_to_address, contract_address, event_inputs from ethereum.core.fact_event_logs where origin_to_address = lower('0x4D9079Bb4165aeb4084c526a32695dCfd2F77381') and contract_address = lower('0x4D9079Bb4165aeb4084c526a32695dCfd2F77381') and origin_from_address not in ('0x428ab2ba90eba0a4be7af34c9ac451ab061ac010','0xf7bac63fc7ceacf0589f25454ecf5c2ce904997c') and tx_status = 'SUCCESS'), AcrossTransferTable as ( select block_timestamp, tx_hash, symbol, amount, amount_usd from ethereum.core.ez_token_transfers where tx_hash in (select distinct(tx_hash) from MainAcrossTable) union ALL select block_timestamp, tx_hash, 'ETH' as symbol, amount, amount_usd from ethereum.core.ez_eth_transfers where tx_hash in (select distinct(tx_hash) from MainAcrossTable)), AcrossFinalTable as ( select t1.*, destinationChainId, origin_from_address from AcrossTransferTable t1 left join MainAcrossTable t2 on t1.tx_hash = t2.tx_hash), hopbridge1 as ( select tx_hash from ethereum.core.fact_event_logs where contract_address in ('0xb8901acb165ed027e32754e0ffe830802919727f','0x3666f603cc164936c1b87e207f36beba4ac5f18a','0x3e4a3a4796d16c0cd582c382691998f7c06420b6','0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1','0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2') and tx_status = 'SUCCESS'), hopbridge2 as ( select block_timestamp, tx_hash, origin_from_address, 'ETH' as symbol, amount_usd from ethereum.core.ez_eth_transfers where tx_hash in (select distinct tx_hash from hopbridge1) union ALL select block_timestamp, tx_hash, origin_from_address, symbol, amount_usd from ethereum.core.ez_token_transfers where tx_hash in (select distinct tx_hash from hopbridge1)), SynapseBridge1 as ( select tx_hash from ethereum.core.fact_event_logs where contract_address in ('0x2796317b0ff8538f253012862c06787adfb8ceb6')), SynapseBridge2 as ( select block_timestamp, tx_hash, origin_from_address, 'ETH' as symbol, amount_usd from ethereum.core.ez_eth_transfers where tx_hash in (select distinct tx_hash from SynapseBridge1) union ALL select block_timestamp, tx_hash, origin_from_address, symbol, amount_usd from ethereum.core.ez_token_transfers where tx_hash in (select distinct tx_hash from SynapseBridge1)) select 'Across' as Bridge_Name, case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd <= 100 then '$10 - $100' when amount_usd >= 100 and amount_usd <= 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd <= 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) as TX_Count from AcrossFinalTable group by 1,2 union ALL select 'Hop' as Bridge_Type, case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd <= 100 then '$10 - $100' when amount_usd >= 100 and amount_usd <= 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd <= 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) as TX_Count from hopbridge2 group by 1,2 union ALL select 'Synapse' as Bridge_Type, case when amount_usd < 10 then 'Less Than $10' when amount_usd >= 10 and amount_usd <= 100 then '$10 - $100' when amount_usd >= 100 and amount_usd <= 1000 then '$100 - $1000' when amount_usd >= 1000 and amount_usd <= 10000 then '$1000 - $10000' else 'More Than $10000' end as type, count (distinct tx_hash) as TX_Count from synapsebridge2 group by 1,2

Across Protocol Airdrop