-- forked from angeal / Wormhole: Solana @ https://flipsidecrypto.xyz/angeal/q/Qrcq4w7tRhhC/wormhole-solana -- first find all wormhole transactions from the program id WITH t0 AS ( SELECT DISTINCT tx_id, block_timestamp -- , signers[0]::string AS signer, COALESCE(signers[1]::string, '') AS signer1 FROM solana.core.fact_events e WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND program_id = 'wormDTUJ6AWPNvk59vGQbDvGJmqbDTdgWgAqcLBCgUb' ), -- next get the signer (sender address) and the token volume from the instructions t1 AS ( SELECT t.tx_id , t.block_timestamp , t.signers[0]::string AS address , DATEADD('seconds', 60 * 30, t.block_timestamp) AS mx_timestamp , t.instructions[1]:parsed:info:lamports::int * POWER(10, -9) AS volume FROM solana.core.fact_transactions t JOIN t0 ON t0.block_timestamp = t.block_timestamp AND t0.tx_id = t.tx_id WHERE t.block_timestamp >= '2021-02-01' AND t.block_timestamp < '2023-02-01' AND succeeded AND COALESCE(t.instructions[1]:parsed:info:lamports::int, 0) > 0 AND t.pre_token_balances[0]:mint::string = 'So11111111111111111111111111111111111111112' AND ARRAY_SIZE(t.inner_instructions[0]:instructions) IN (4, 6) AND volume >= 0.1 -- now we'll join on data from each other chain to form the full picture ), t2 AS ( SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'Ethereum' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM ethereum.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xd31a59c85ae9d8edefec411d448f90841571b89c' AND from_address = '0x0000000000000000000000000000000000000000' UNION SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'Polygon' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM polygon.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xd93f7e271cb87c23aaa73edc008a79646d1f9912' AND from_address = '0x0000000000000000000000000000000000000000' UNION SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'Avalanche' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM avalanche.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xfe6b19286885a4f7f55adad09c3cd1f906d2478f' AND from_address = '0x0000000000000000000000000000000000000000' UNION SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'Optimism' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM optimism.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xba1cf949c382a32a09a17b2adf3587fc7fa664f1' AND from_address = '0x0000000000000000000000000000000000000000' UNION SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'BSC' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM bsc.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xfa54ff1a158b5189ebba6ae130ced6bbd3aea76e' AND from_address = '0x0000000000000000000000000000000000000000' UNION SELECT tx_hash , to_address , block_timestamp AS bridge_timestamp , 'Arbitrum' AS chain , raw_amount * POWER(10, -9) AS bridge_volume FROM arbitrum.core.fact_token_transfers WHERE block_timestamp >= '2021-02-01' AND block_timestamp < '2023-02-01' AND contract_address = '0xb74da9fe2f96b9e0a5f4a3cf0b92dd2bec617124' -- AND contract_address = '0x2bcc6d6cdbbdc0a4071e48bb3b969b06b3330c07' AND from_address = '0x0000000000000000000000000000000000000000' ), t3 AS ( SELECT t1.* , t2.* , ROW_NUMBER() OVER (PARTITION BY t1.tx_id ORDER BY t2.bridge_timestamp) AS rn FROM t1 LEFT JOIN t2 ON ROUND(t2.bridge_volume, 3) = ROUND(t1.volume, 3) AND t2.bridge_timestamp >= t1.block_timestamp AND t2.bridge_timestamp <= t1.mx_timestamp ), t4 AS ( SELECT * , ROW_NUMBER() OVER (PARTITION BY tx_hash ORDER BY bridge_timestamp) AS rn2 FROM t3 WHERE rn <= 1 ) SELECT DISTINCT address FROM t4 WHERE chain IS NOT NULL --ORDER BY block_timestamp DESC

Query	Creator	Created
deBridge Final 1 copy bsc arbitrum base ethereum optimism	commanderjoseph-761gwX	Oct 28, 2024
AVAX ZRO avalanche evm	commanderjoseph-761gwX	Jun 29, 2024
Wormhole: Solana copy bsc arbitrum ethereum optimism avalanche	commanderjoseph-761gwX	Mar 13, 2024

commanderjoseph-761gwX