--credit : alik110 with standardbridge as ( select block_timestamp, tx_hash, origin_from_address, 'ETH' as symbol, amount_usd from ethereum.core.ez_eth_transfers where eth_to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1' union ALL select block_timestamp, tx_hash, origin_from_address, symbol, amount_usd from ethereum.core.ez_token_transfers where to_address = '0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'), hopbridge1 as ( select tx_hash from ethereum.core.fact_event_logs where event_inputs:chainId = '10' and 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)), acrossbridge1 as ( select block_timestamp, tx_hash, regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') as d, ethereum.public.udf_hex_to_int(d[0]) / pow(10,18) as amount, ethereum.public.udf_hex_to_int(d[1]) as originChainId, ethereum.public.udf_hex_to_int(d[2]) as destinationChainId, origin_from_address, origin_to_address, contract_address, event_inputs from ethereum.core.fact_event_logs where tx_status = 'SUCCESS' and origin_to_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381' and contract_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381' and origin_from_address not in ('0x428ab2ba90eba0a4be7af34c9ac451ab061ac010','0xf7bac63fc7ceacf0589f25454ecf5c2ce904997c')), acrossbridge2 as ( select block_timestamp, tx_hash, symbol, amount_usd from ethereum.core.ez_token_transfers where tx_hash in (select distinct tx_hash from acrossbridge1) union ALL select block_timestamp, tx_hash, 'ETH' as symbol, amount_usd from ethereum.core.ez_eth_transfers where tx_hash in (select distinct tx_hash from acrossbridge1)), acrossbridge3 as ( select t1.*, destinationChainId, t2.origin_from_address, t2.block_timestamp from acrossbridge2 t1 left join acrossbridge1 t2 using (tx_hash)) select 'Standard Bridge' 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 Bridges_Count from standardbridge group by 1,2 union ALL select 'Hop Bridge' 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 Bridges_Count from hopbridge2 group by 1,2 union ALL select 'Across Bridge' 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 Bridges_Count from acrossbridge3 where destinationChainId = '10' group by 1,2

Optimism Bridge Analysis