WITH hop_deposit_token as ( SELECT date_trunc('hour', tt.block_timestamp) as block_timestamp, tt.tx_hash, tt.origin_from_address as from_address, 'hop' as platform, CASE WHEN tokenflow_eth.hextoint(substr(fc.input_data, 11, 64)) = 42161 THEN 'arbitrum' -- Arbitrum: L1 Gateway Router WHEN tokenflow_eth.hextoint(substr(fc.input_data, 11, 64)) = 10 THEN 'optimism' -- optimism: Gateway WHEN tokenflow_eth.hextoint(substr(fc.input_data, 11, 64)) = 137 THEN 'polygon' -- Polygon (Matic): Bridge END as destination, CASE WHEN tt.origin_to_address = '0xb8901acb165ed027e32754e0ffe830802919727f' then 'WETH' WHEN tt.origin_to_address = '0x3666f603cc164936c1b87e207f36beba4ac5f18a' then 'USDC' WHEN tt.origin_to_address = '0x3e4a3a4796d16c0cd582c382691998f7c06420b6' then 'USDT' WHEN tt.origin_to_address = '0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2' then 'MATIC' WHEN tt.origin_to_address = '0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1' then 'DAI' end bridge, tt.raw_amount/pow(10, dc.contract_metadata:decimals) as amount FROM ethereum.core.fact_token_transfers as tt join ethereum.core.dim_contracts as dc on tt.contract_address = dc.address join ethereum.core.fact_transactions as fc on tt.tx_hash = fc.tx_hash WHERE tt.origin_to_address in (lower('0xb8901acb165ed027e32754e0ffe830802919727f'), -- Hop Protocol: Ethereum Bridge lower('0x3666f603cc164936c1b87e207f36beba4ac5f18a'), -- Hop Protocol: USDC Bridge lower('0x3e4a3a4796d16c0cd582c382691998f7c06420b6'), -- Hop Protocol: USDT Bridge lower('0x22b1cbb8d98a01a3b71d034bb899775a76eb1cc2'), -- Hop Protocol: MATIC Bridge lower('0x3d4cc8a61c7528fd86c55cfe061a78dcba48edd1')) -- Hop Protocol: DAI Bridge AND destination in ('arbitrum', 'optimism', 'polygon') AND amount > 0 ORDER BY tt.block_timestamp, destination, bridge ) , hop_deposit_eth as ( SELECT date_trunc('hour', block_timestamp) as block_timestamp, tx_hash, from_address, 'hop' as platform, CASE WHEN tokenflow_eth.hextoint(substr(input_data, 11, 64)) = 42161 THEN 'arbitrum' -- Arbitrum: L1 Gateway Router WHEN tokenflow_eth.hextoint(substr(input_data, 11, 64)) = 10 THEN 'optimism' -- optimism: Gateway WHEN tokenflow_eth.hextoint(substr(input_data, 11, 64)) = 137 THEN 'polygon' -- Polygon (Matic): Bridge END as destination, 'WETH' as bridge, eth_value as amount FROM ethereum.core.fact_transactions WHERE to_address = lower('0xb8901acb165ed027e32754e0ffe830802919727f') -- Hop Protocol: Ethereum Bridge AND destination in ('arbitrum', 'optimism', 'polygon') AND amount > 0 ORDER BY block_timestamp, destination, bridge ) , native_deposit_token as ( SELECT date_trunc('hour', tt.block_timestamp) as block_timestamp, tt.tx_hash, tt.origin_from_address as from_address, 'native' as platform, CASE WHEN origin_to_address = lower('0x72ce9c846789fdb6fc1f34ac4ad25dd9ef7031ef') THEN 'arbitrum' -- Arbitrum: L1 Gateway Router WHEN origin_to_address = lower('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1') THEN 'optimism' -- optimism: Gateway WHEN origin_to_address = lower('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77') THEN 'polygon' -- Polygon (Matic): Bridge END as destination, dc.symbol as bridge, tt.raw_amount/pow(10, dc.contract_metadata:decimals) as amount FROM ethereum.core.fact_token_transfers as tt join ethereum.core.dim_contracts as dc on contract_address = address WHERE origin_to_address in (lower('0x72ce9c846789fdb6fc1f34ac4ad25dd9ef7031ef'), -- Arbitrum: L1 Gateway Router lower('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'), -- optimism: Gateway lower('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77')) -- Polygon (Matic): Bridge AND symbol in ('WETH', 'USDC', 'USDT', 'MATIC', 'DAI') AND amount > 0 ORDER BY block_timestamp, destination, bridge ) , native_deposit_eth as ( SELECT date_trunc('hour', block_timestamp) as block_timestamp, tx_hash, from_address, 'native' as platform, CASE WHEN to_address = lower('0x4dbd4fc535ac27206064b68ffcf827b0a60bab3f') THEN 'arbitrum' -- arbitrum_inbox WHEN to_address = lower('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1') THEN 'optimism' -- optimism: Gateway WHEN to_address = lower('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77') THEN 'polygon' -- Polygon (Matic): Bridge END as destination, 'WETH' as bridge, eth_value as amount FROM ethereum.core.fact_transactions WHERE to_address in (lower('0x4dbd4fc535ac27206064b68ffcf827b0a60bab3f'), -- arbitrum_inbox lower('0x99c9fc46f92e8a1c0dec1b1747d010903e884be1'), -- optimism: Gateway lower('0xA0c68C638235ee32657e8f720a23ceC1bFc77C77')) -- Polygon (Matic): Bridge AND amount > 0 ORDER BY block_timestamp, destination, bridge ) , deposit_bridge as ( SELECT * FROM native_deposit_token UNION SELECT * FROM native_deposit_eth UNION SELECT * FROM hop_deposit_token UNION SELECT * FROM hop_deposit_eth ORDER BY block_timestamp ) , user_dist as ( select user_address, erc20_vol, case when erc20_vol < 100 then '1.Shimp: $ < 100' when erc20_vol >= 100 and erc20_vol < 1000 then '2.Crab: $ < 1k' when erc20_vol >= 1000 and erc20_vol < 10000 then '3.Octopus: $ < 10k' when erc20_vol >= 10000 and erc20_vol < 100000 then '4.Fish: $ < 100k' when erc20_vol >= 100000 and erc20_vol < 1000000 then '5.Shark: $ < 1m' when erc20_vol >= 1000000 then '6.Whale: $ > 1m' end as address_type from ( select user_address, sum(amount_usd) as erc20_vol from flipside_prod_db.ethereum.erc20_balances where balance_date::date = CURRENT_DATE - 1 group by user_address having erc20_vol > 0 ) ) , whale_list as ( SELECT user_address FROM user_dist WHERE address_type = '6.Whale: $ > 1m' ) SELECT platform, destination, count(distinct(user_address)) as whale_count, whale_count/(select count(*) from user_dist where address_type = '6.Whale: $ > 1m') * 100 as whale_percent FROM deposit_bridge as db join whale_list as wl on db.from_address = wl.user_address GROUP BY platform, destination ORDER BY platform, destination

Hop Whales