WITH eth_bridge_transfers AS ( SELECT tx_hash , block_timestamp , eth_from_address AS from_address , 'ETH' AS token_symbol , amount , amount_usd FROM ethereum.core.ez_eth_transfers WHERE block_timestamp::date BETWEEN '2021-07-01' AND '2022-06-15' AND eth_to_address IN ( '0x98f3c9e6e3face36baad05fe09d375ef1464288b' , '0xf92cd566ea4864356c5491c177a430c222d7e678' , '0x3ee18b2214aff97000d974cf647e7c347e8fa585' ) ), token_bridge_transfers AS ( SELECT tr.tx_hash , tr.block_timestamp , tr.from_address , tr.contract_address AS token_contract_address , tr.symbol AS token_symbol , tr.amount , tr.amount_usd FROM ethereum.core.ez_token_transfers AS tr WHERE tr.block_timestamp::date BETWEEN '2021-07-01' AND '2022-06-15' AND tr.to_address IN ( '0x98f3c9e6e3face36baad05fe09d375ef1464288b' , '0xf92cd566ea4864356c5491c177a430c222d7e678' , '0x3ee18b2214aff97000d974cf647e7c347e8fa585' ) ), all_bridge_transfers AS ( SELECT tx_hash, block_timestamp, from_address, token_symbol, amount_usd FROM eth_bridge_transfers UNION ALL SELECT tx_hash, block_timestamp, from_address, token_symbol, amount_usd FROM token_bridge_transfers WHERE amount_usd IS NOT NULL ), bridge_wallets AS ( SELECT from_address AS address , sum(amount_usd) AS bridged_amount_usd FROM all_bridge_transfers WHERE token_symbol NOT IN ('NEXM','CHN') GROUP BY 1 ), wallet_token_balances AS ( SELECT wallet_address , symbol , contract_address , sum(amount_usd) AS amount_usd , sum(amount) AS amount FROM ( -- Token outflow SELECT from_address AS wallet_address , symbol , contract_address , -sum(amount_usd) AS amount_usd , -sum(amount) AS amount FROM ethereum.core.ez_token_transfers WHERE block_timestamp::date <= '2022-06-15' AND from_address IN (SELECT address FROM bridge_wallets) GROUP BY 1,2,3 UNION ALL -- Token inflow SELECT to_address AS wallet_address , symbol , contract_address , sum(amount_usd) AS amount_usd , sum(amount) AS amount FROM ethereum.core.ez_token_transfers WHERE block_timestamp::date <= '2022-06-15' AND to_address IN (SELECT address FROM bridge_wallets) GROUP BY 1,2,3 UNION ALL -- ETH outflow SELECT eth_from_address AS wallet_address , 'ETH' AS symbol , NULL AS contract_address , -sum(amount_usd) AS amount_usd , -sum(amount) AS amount FROM ethereum.core.ez_eth_transfers WHERE block_timestamp::date <= '2022-06-15' AND eth_from_address IN (SELECT address FROM bridge_wallets) GROUP BY 1,2,3 UNION ALL -- ETH outflow (gas) SELECT ft.from_address AS wallet_address , 'ETH' AS symbol , NULL AS contract_address , -sum(ft.tx_fee * p.price) AS amount_usd , -sum(ft.tx_fee) AS amount FROM ethereum.core.fact_transactions AS ft LEFT JOIN ethereum.core.fact_hourly_token_prices AS p ON p.hour = date_trunc('hour', ft.block_timestamp) AND p.symbol = 'WETH' WHERE block_timestamp::date <= '2022-06-15' AND from_address IN (SELECT address FROM bridge_wallets) GROUP BY 1,2,3 UNION ALL -- ETH inflow SELECT eth_to_address AS wallet_address , 'ETH' AS symbol , NULL AS contract_address , sum(amount_usd) AS amount_usd , sum(amount) AS amount FROM ethereum.core.ez_eth_transfers WHERE block_timestamp::date <= '2022-06-15' AND eth_to_address IN (SELECT address FROM bridge_wallets) GROUP BY 1,2,3 ) AS prep WHERE amount_usd IS NOT NULL GROUP BY 1,2,3 HAVING sum(amount) > 0 ), wallet_asset_values AS ( SELECT wallet_address , sum(amount_usd) AS amount_usd FROM wallet_token_balances GROUP BY 1 ), wallets_summary AS ( SELECT bw.address , bw.bridged_amount_usd , wav.amount_usd AS assets_amount_usd FROM bridge_wallets AS bw LEFT JOIN wallet_asset_values AS wav ON wav.wallet_address = bw.address ), bridge_wallets__bins AS ( SELECT (CASE WHEN assets_amount_usd < 100 THEN ' 0 to 100' WHEN assets_amount_usd < 1000 THEN ' 100 to 1,000' WHEN assets_amount_usd < 10000 THEN ' 1,000 to 10,000' WHEN assets_amount_usd < 100000 THEN ' 10,000 to 100,000' WHEN assets_amount_usd < 1000000 THEN '100,000 to 1,000,000' ELSE '> 1,000,000' END) AS bin , count(*) AS wallets FROM wallets_summary GROUP BY 1 ) SELECT * FROM bridge_wallets__bins LIMIT 100000

The Bridge from Ethereum to Solana