Skip to content

Home

Jump to bottom
Suleyman edited this page Jun 25, 2023 · 1 revision

Welcome to the ARb wiki! with celer_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', evt_block_time) as block_date, amount, evt_tx_hash as tx_hash from celer_ethereum.Bridge_evt_Send --celer.Bridge_evt_Send where dstChainId = 42161 -- Arbitrum Chain ID and evt_block_time >= now() - interval '15 days' and token in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    value as amount,
    hash as tx_hash
from optimism.transactions
where `to` = '0x9d39fc627a6d9d9f8c831c16995b209548cc3401' 
    and substring(data, 1, 10) = '0x3f2e5fc3' -- sendNative
    and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1'
    and block_time >= now() - interval '15 days'

union all

select 'BNB' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64 * 2, 64)) as amount,
    hash as tx_hash
from bnb.transactions
where block_time >= now() - interval '15 days'
    and `to` = '0xdd90e5e87a2081dcf0391920868ebc2ffb81a1af'
    and substring(data, 1, 10) = '0xa5977fbb' -- send
    and substring(data, 11 + 64 * 1, 64) = '0000000000000000000000002170ed0880ac9a755fd29b2688956bd959f933f8'
    and substring(data, 11 + 64 * 3, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1'

),

celer_arbitrum_transactions_summary as ( select 'Celer Network' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from celer_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

-- Bungee: Ethereum + BNB + Optimism + Gnosis bungee_arbitrum_transactions as ( -- Transferred through arbitrum native bridge select date_trunc('day', evt_block_time) as block_date, value as amount, evt_tx_hash from erc20_ethereum.evt_Transfer -- erc20.ERC20_evt_Transfer where to = '0x852c5de08b9beb014cad171c16b12a8d7456ea3f' -- socket bungee NativeArbitrumImpl and evt_block_time >= now() - interval '15 days' and contract_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'

union all

-- Transferred through Hop protocol. 
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3, 64)) as amount,
    tx_hash
from ethereum.logs l
inner join (
    select hash
    from ethereum.transactions
    where block_time >= now() - interval '15 days'
        and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
        and data like '%b8901acb165ed027e32754e0ffe830802919727f%'  -- Hop Protocol: Ethereum Bridge
) t on l.tx_hash = t.hash
where  topic1 = '0x0a0607688c86ec1775abcdbab7b33a3a35a6c9cde677c9be880150c231cc6b0b'  -- TransferSentToL2
    and topic2 = '0x000000000000000000000000000000000000000000000000000000000000a4b1'   -- 42161
    and block_time >= now() - interval '15 days'

union all

-- Transferred through Across protocol
 select date_trunc('day', block_time) as block_date,
        bytea2numeric_v2(substring(data, 3, 64)) as amount,
        tx_hash
    from ethereum.logs l
    inner join (
        select hash
        from ethereum.transactions
        where block_time >= now() - interval '15 days'
            and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
            and data like '%c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2%'  -- WETH
    ) t on l.tx_hash = t.hash
    where contract_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'   -- Across Protocol
        and topic1 = '0x4a4fc49abd237bfd7f4ac82d6c7a284c69daaea5154430cff04ad7482c6c4254'  -- FundsDeposited
        and substring(data, 3 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1'   -- 42161
        and topic3 = '0x000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
    and block_time >= now() - interval '15 days'

union all

-- Transferred through Celer protocol
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 4, 64)) as amount,
    tx_hash
from ethereum.logs l
inner join (
    select hash
    from ethereum.transactions
    where block_time >= now() - interval '15 days'
        and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
        and (data like '%c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2%'  -- WETH
            or data like '%eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee%') -- Celer WETH substition
) t on l.tx_hash = t.hash
where contract_address = '0x5427fefa711eff984124bfbb1ab6fbf5e3da1820'   -- Celer Network: cBridge V2 
    and topic1 = '0x89d8051e597ab4178a863a5190407b98abfeff406aa8db90c59af76612e58f01'  -- Send
    and substring(data, 3 + 64 * 5, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1'   -- 42161
    and substring(data, 3 + 64 * 3, 64) = '000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- WETH
    and block_time >= now() - interval '15 days'

union all

-- BNB: Celer Protocol
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 4, 64)) as amount,
    tx_hash
from bnb.logs l
inner join (
    select hash
    from bnb.transactions
    where block_time >= now() - interval '15 days'
        and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
        and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- chainID 42161
        and data like '%2170ed0880ac9a755fd29b2688956bd959f933f8%'  -- Binance-Peg Ethereum Token (ETH) 
) t on l.tx_hash = t.hash
where contract_address = '0xdd90e5e87a2081dcf0391920868ebc2ffb81a1af'   -- Celer Network: cBridge 2.0 2
  and topic1 = '0x89d8051e597ab4178a863a5190407b98abfeff406aa8db90c59af76612e58f01' -- Send
  and right(substring(data, 3 + 64 * 3, 64), 40) = '2170ed0880ac9a755fd29b2688956bd959f933f8' -- Binance-Peg Ethereum Token (ETH) 
and block_time >= now() - interval '15 days'

union all

-- BNB: Anyswap
select date_trunc('day', block_time) as block_date,
        bytea2numeric_v2(substring(data, 3, 64)) as amount,
        tx_hash
from bnb.logs l
inner join (
    select hash
    from bnb.transactions
    where block_time >= now() - interval '15 days'
        and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
        and data like '%000000000000000000000000000000000000000000000000000000000000a4b1%' -- chainID 42161
        and data like '%debb1d6a2196f2335ad51fbde7ca587205889360%'  -- Binance-Peg Ethereum Token
) t on l.tx_hash = t.hash
where contract_address = '0xd1c5966f9f5ee6881ff6b261bbeda45972b1b5f3'   -- AnyswapImplL2
  and topic1 = '0x97116cf6cd4f6412bb47914d6db18da9e16ab2142f543b86e207c24fbd16b23a' -- LogAnySwapOut
  and topic2 = '0x000000000000000000000000debb1d6a2196f2335ad51fbde7ca587205889360' -- Binance-Peg Ethereum Token
  and substring(data, 3 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' --42161
and block_time >= now() - interval '15 days'

union all

-- BNB: Hyphen
select date_trunc('day', block_time) as block_date,
        bytea2numeric_v2(substring(data, 3 + 64, 64)) as amount,
        tx_hash
from bnb.logs l
inner join (
    select hash
    from bnb.transactions
    where block_time >= now() - interval '15 days'
        and `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
        and data like '%000000000000000000000000000000000000000000000000000000000000a4b1%' -- chainID 42161
        and data like '%2170ed0880ac9a755fd29b2688956bd959f933f8%'  -- Binance-Peg Ethereum Token (ETH) 
) t on l.tx_hash = t.hash
where contract_address = '0x94d3e62151b12a12a4976f60edc18459538faf08'   -- AnyswapImplL2
  and topic1 = '0x522e11fa05593b306c8df10d2b0b8e01eec48f9d0a9427a7a93f21ff90d66fb1' -- Deposit
  and topic3 = '0x0000000000000000000000002170ed0880ac9a755fd29b2688956bd959f933f8' -- Binance-Peg Ethereum Token (ETH) 
  and substring(data, 3, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' --42161
and block_time >= now() - interval '15 days'

union all

-- Optimism: Send
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 4, 64)) as amount,
    tx_hash
from optimism.logs l
inner join (
    select hash
    from optimism.transactions
    where `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
    and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and block_time >= now() - interval '15 days'
) t on l.tx_hash = t.hash
where topic1 = '0x89d8051e597ab4178a863a5190407b98abfeff406aa8db90c59af76612e58f01' --Send
    and data like '%0000000000000000000000004200000000000000000000000000000000000006%' -- Wrapped Ether
    and data like '%000000000000000000000000000000000000000000000000000000000000a4b1%' --42161
    and block_time >= now() - interval '15 days'

union all

-- Optimism: Send through Hop
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 4, 64)) as amount,
    tx_hash
from optimism.logs l
inner join (
    select hash
    from optimism.transactions
    where `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
    and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and data like '%eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee%'  -- WETH substiion
    and block_time >= now() - interval '15 days'
) t on l.tx_hash = t.hash
where topic1 = '0xe35dddd4ea75d7e9b3fe93af4f4e40e778c3da4074c9d93e7c6536f1e803c1eb' --TransferSent
    and topic3 = '0x000000000000000000000000000000000000000000000000000000000000a4b1' --42161
    and block_time >= now() - interval '15 days'

union all

-- Optimism: Send through Across
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 4, 64)) as amount,
    tx_hash
from optimism.logs l
inner join (
    select hash
    from optimism.transactions
    where `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry
    and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and data like '%eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee%'  -- WETH substiion
    and block_time >= now() - interval '15 days'
) t on l.tx_hash = t.hash
where topic1 = '0x4a4fc49abd237bfd7f4ac82d6c7a284c69daaea5154430cff04ad7482c6c4254' --FundsDeposited
    and substring(data, 3 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' --42161
    and block_time >= now() - interval '15 days'

union all

-- Gnosis
select date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3, 64)) as amount,
    hash as tx_hash
from gnosis.logs l
inner join (
    select hash
    from gnosis.transactions
    where `to` = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0'   -- Socket: Registry
        and data like '%000000000000000000000000000000000000000000000000000000000000a4b1%' -- 42161
        and data like '%0000000000000000000000006a023ccd1ff6f2045c3309768ead9e68f978f6e1%' -- WETH
        and block_time >= now() - interval '15 days'
) t on l.tx_hash = t.hash
where topic1 = '0xe35dddd4ea75d7e9b3fe93af4f4e40e778c3da4074c9d93e7c6536f1e803c1eb'   --
    and topic3 = '0x000000000000000000000000000000000000000000000000000000000000a4b1' -- 42161
    and block_time >= now() - interval '15 days'

),

bungee_arbitrum_transactions_summary as ( select 'Bungee' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from bungee_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

-- Across: Ethereum + Optimism across_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', call_block_time) as block_date, amount, call_tx_hash as tx_hash from across_v2_ethereum.Ethereum_SpokePool_call_deposit --across_v2.Ethereum_SpokePool_call_deposit where destinationChainId = 42161 and call_block_time >= now() - interval '15 days' and originToken in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64 * 3, 64)) as amount,
    hash as tx_hash
from optimism.transactions
where `to` = '0xa420b2d1c0841415a695b81e5b867bcd07dff8c9' -- Across Protocol: Optimism Spoke Pool V2
  and substring(data, 11 + 64 * 1, 64) = '0000000000000000000000004200000000000000000000000000000000000006' -- originToken: Wrapped Ether 
  and substring(data, 11 + 64 * 3, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and block_time >= now() - interval '15 days'

),

across_arbitrum_transactions_summary as ( select 'Across' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from across_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

-- Multichain: Ethereum + BNB + Optimism multichain_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', block_time) as block_date, bytea2numeric_v2(substring(data, 3, 64)) as amount, tx_hash from ethereum.logs where topic1 = '0x97116cf6cd4f6412bb47914d6db18da9e16ab2142f543b86e207c24fbd16b23a' -- LogAnySwapOut, Multichain router event and conv(substring(data, 3 + 2*64, 64), 16, 10) = 42161 -- Arbitrum chain id and block_time >= now() - interval '15 days' and concat('0x', right(topic2, 40)) in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x0615dbba33fe61a31c7ed131bda6655ed76748b1') -- WETH, anyETH

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3, 64)) as amount,
    tx_hash
from optimism.logs l
inner join (
    select hash
    from optimism.transactions
    where `to` = '0xdc42728b0ea910349ed3c6e1c9dc06b5fb591f98'
        and substring(data, 11, 64) = '000000000000000000000000965f84d915a9efa2dd81b653e3ae736555d945f4' -- Wrapped Ether (anyWETH)
        and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- toChainID: 42161
        and block_time >= now() - interval '15 days'
) t on t.hash = l.tx_hash
where `contract_address` = '0xdc42728b0ea910349ed3c6e1c9dc06b5fb591f98'
    and substring(data, 3 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and block_time >= now() - interval '15 days'

union all

select 'BNB' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64 * 2, 64)) as amount,
    hash as tx_hash
from bnb.transactions
where `to` = '0xd1c5966f9f5ee6881ff6b261bbeda45972b1b5f3'
    and substring(data, 11 + 64 * 0, 64) = '000000000000000000000000debb1d6a2196f2335ad51fbde7ca587205889360' -- Binance-Peg Ethereum Token (anyETH)
    and substring(data, 11 + 64 * 3, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- 42161
    and block_time >= now() - interval '15 days'

),

multichain_arbitrum_transactions_summary as ( select 'Multichain' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from multichain_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

-- Hop: ethereum + optimism + gnosis hop_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', call_block_time) as block_date, amount, call_tx_hash as tx_hash from hop_protocol_ethereum.L1_ETH_Bridge_call_sendToL2 l -- hop_ethereum.L1_ETH_Bridge_call_sendToL2 l left join ( select hash from ethereum.transactions where block_time >= now() - interval '15 days' and to = '0xc30141b657f4216252dc59af2e7cdb9d8792e1b0' -- Socket: Registry (Bungee) and data like '%b8901acb165ed027e32754e0ffe830802919727f%' -- Hop Protocol: Ethereum Bridge ) t on l.call_tx_hash = t.hash where chainId = 42161 and call_block_time >= now() - interval '15 days' and t.hash is null -- Exclude data from others

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64 * 2, 64)) as amount,
    hash as tx_hash
from optimism.transactions
where `to` = '0x86ca30bef97fb651b8d866d45503684b90cb3312'
    and substring(data, 71, 4) = 'a4b1'   -- 42161
    and block_time >= now() - interval '15 days'
  
union all

select 'Gnosis' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64 * 2, 64)) as amount,
    hash as tx_hash
from gnosis.transactions
where `to` = '0x03d7f750777ec48d39d080b020d83eb2cb4e3547'
    and substring(data, 71, 4) = 'a4b1'
    and block_time >= now() - interval '15 days'

),

hop_arbitrum_transactions_summary as ( select 'Hop Protocol' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from hop_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

composable_arbitrum_transactions as ( select date_trunc('day', call_block_time) as block_date, amount, call_tx_hash as tx_hash from composable_ethereum.L2Vault_call_withdrawTo -- composable.L2Vault_call_withdrawTo where remoteNetworkID = 42161 and call_block_time >= now() - interval '15 days' and tokenAddress in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') -- WETH ),

composable_arbitrum_transactions_summary as ( select 'Composable' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from composable_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

-- Connext: Ethereum + BNB + Optimism connext_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', block_time) as block_date, amount, tx_hash from ( select block_time, user, tx_hash, get_json_object(txData, '$.receivingChainTxManagerAddress') as receivingChainTxManagerAddress, get_json_object(txData, '$.sendingAssetId') as sendingAssetId, get_json_object(txData, '$.receivingChainId') as receivingChainId, get_json_object(txData, '$.amount') as amount, txData from ( select t.evt_block_time as block_time, t.user, get_json_object(t.args, '$.txData') as txData, t.evt_tx_hash as tx_hash from xpollinate_ethereum.TransactionManager_evt_TransactionFulfilled t ) t1 ) t2 where receivingChainId = 42161 and block_time >= now() - interval '15 days' and sendingAssetId in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x0000000000000000000000000000000000000000') -- WETH

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 13, 64)) as amount,
    tx_hash
from optimism.logs
where `contract_address` = '0x31efc4aeaa7c39e54a33fdc3c46ee2bd70ae0a09'
    and topic1 = '0x88fbf1dbc326c404155bad4643bd0ddadd23f0636929c66442f0433208b2c905'
    and substring(data, 3 + 64 * 12, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId: 42161
    and block_time >= now() - interval '15 days'

union all

select 'BNB' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 13, 64)) as amount,
    tx_hash
from bnb.logs l
inner join (
        select hash
        from bnb.transactions
        where `to` = '0x2a9ea5e8cddf40730f4f4f839f673a51600c314e'
            and substring(data, 355, 40) = '2170ed0880ac9a755fd29b2688956bd959f933f8'
            and block_time >= now() - interval '15 days'
    ) t on t.hash = l.tx_hash
where block_time >= now() - interval '15 days'
    and contract_address = '0x2a9ea5e8cddf40730f4f4f839f673a51600c314e'
    and topic1 = '0x88fbf1dbc326c404155bad4643bd0ddadd23f0636929c66442f0433208b2c905'
    and substring(data, 3 + 64 * 12, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- 42161

),

connext_arbitrum_transactions_summary as ( select 'Connext' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from connext_arbitrum_transactions t where block_date >= now() - interval '15 days' group by block_date ),

-- Hashflow: Ethereum + Optimism hashflow_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', evt_block_time) as block_date, baseTokenAmount as amount, evt_tx_hash as tx_hash from hashflow_ethereum.Pool_evt_LzTrade -- hashflow.Pool_evt_LzTrade where dstChainId = 10 and evt_block_time >= now() - interval '15 days' and baseToken in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x0000000000000000000000000000000000000000') -- WETH

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    value as amount,
    hash as tx_hash
from optimism.transactions
where `to` = '0x54a06197130e02aa0244c4a413f70c52348c3610'
    and block_time >= now() - interval '15 days'
    and substring(data, 266, 1) = 'a'
    and substring(data, 483, 40) = '0000000000000000000000000000000000000000'

),

hashflow_arbitrum_transactions_summary as ( select 'Hashflow' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from hashflow_arbitrum_transactions t where block_date >= now() - interval '15 days' group by block_date ),

-- BoringDAO: Ethereum + Optimism boringdao_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', block_time) as block_date, bytea2numeric_v2(substring(data, 11 + 64 * 3, 64)) as amount, hash as tx_hash from ethereum.transactions where to = '0xecad1ab3464eccc7536af6afee414df873495616' -- BoringDAO: Bridge and substring(data, 1, 10) = '0xdf7e600a' -- crossOut and substring(data, 11 + 64 * 1, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' and block_time >= now() - interval '15 days' and concat('0x', right(substring(data, 11, 64), 40)) in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x0000000000000000000000000000000000000000') -- WETH -- see tx: https://etherscan.io/tx/0XE5BC92598425D3EF667EAF4296ABC4FD6DD4013CF5D4490B2A16408742CB9274

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    value as amount,
    hash as tx_hash
from optimism.transactions
where `to` = '0x29414ec76d79ff238e5e773322799d1c7ca2443f' 
    and substring(data, 1, 10) = '0xdf7e600a' -- 
    and substring(data, 11 + 64 * 1, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1'
    and block_time >= now() - interval '15 days'

),

boringdao_arbitrum_transactions_summary as ( select 'BoringDAO' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from boringdao_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

debridge_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', block_time) as block_date, bytea2numeric_v2(substring(data, 11 + 64, 64)) as amount, hash as tx_hash from ethereum.transactions where to = '0x43de2d77bf8027e25dbd179b491e8d64f38398aa' -- DeBridge and substring(data, 1, 10) = '0xbe297476' -- Send and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' and block_time >= now() - interval '15 days' and concat('0x', right(substring(data, 11, 64), 40)) in ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', '0x0000000000000000000000000000000000000000') -- WETH -- see tx: https://etherscan.io/tx/0x29768be9dd095687dba8d93e5136bbddb8ff3df04635befe60853b09c977648b

union all

select 'BNB' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 11 + 64, 64)) as amount,
    hash as tx_hash
from bnb.transactions
where `to` = '0x43de2d77bf8027e25dbd179b491e8d64f38398aa'   -- DeBridgeGate
and block_time > '2022-06-01'
and substring(data, 0, 10) = '0xbe297476' -- send
and substring(data, 11, 64) = '0000000000000000000000002170ed0880ac9a755fd29b2688956bd959f933f8' -- Binance-Peg Ethereum Token
and substring(data, 11 + 64 * 2, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- 42161

),

debridge_arbitrum_transactions_summary as ( select 'DeBridge' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from debridge_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

degate_arbitrum_transactions as ( select date_trunc('day', block_time) as block_date, value as amount, hash as tx_hash from ethereum.transactions where to = '0xa3af00a4ca8a11b840b1cb190d1c1d66da1546fc' -- DeGate: Hot Wallet and success = true and block_time >= now() - interval '15 days' -- see tx: https://etherscan.io/tx/0xb926b6916fed28b4c2ec6e5bd0c9cc737177e68035232b2e13ffbe9951eae792 ),

degate_arbitrum_transactions_summary as ( select 'DeGate' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from degate_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

router_arbitrum_transactions as ( select date_trunc('day', block_time) as block_date, value as amount, hash as tx_hash from ethereum.transactions where to = '0xf18acc02628009231d7baaf9a7a24c0860dda6cb' -- router protocol and substring(data, 1, 10) = '0x2e09305c' -- depositETH and substring(data, 11, 64) = '0000000000000000000000000000000000000000000000000000000000000005' -- destinationChainID and block_time >= now() - interval '15 days' -- see tx: https://etherscan.io/tx/0x1a758af263b0789bad39b7890692b278dcbb2c114798018df94ba81e582f27ef ),

router_arbitrum_transactions_summary as ( select 'Router' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from router_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

lifi_arbitrum_transactions as ( select 'Ethereum' as block_chain, date_trunc('day', block_time) as block_date, bytea2numeric_v2(substring(data, 3 + 64 * 7, 64)) as amount, -- amount tx_hash from ethereum.logs where topic1 = '0x438f81f3fe94456cd9d98e9073524f1c2bafb3ce75def8ced69f708061ddd5c4' -- LiFiTransferStarted and block_time >= now() - interval '15 days' and substring(data, 3 + 64 * 8, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId = 42161 and ( -- checking for sent ETH/WETH to parse amounts easily substring(data, 3 + 64 * 4, 64) = '0000000000000000000000000000000000000000000000000000000000000000' -- sendingAssetId = ETH or substring(data, 3 + 64 * 4, 64) = '000000000000000000000000c02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- sendingAssetId = WETH )

union all

select 'BNB' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 7, 64)) as amount, -- avg. eth amount
    tx_hash
from bnb.logs
where topic1 = '0x438f81f3fe94456cd9d98e9073524f1c2bafb3ce75def8ced69f708061ddd5c4' -- LiFiTransferStarted
    and block_time >= now() - interval '15 days'
    and substring(data, 3 + 64 * 8, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId = 42161
    --and substring(data, 3 + 64 * 5, 64) = '0000000000000000000000000000000000000000000000000000000000000000' -- receivingAssetId = ETH
    and substring(data, 3 + 64 * 4, 64) = '0000000000000000000000002170ed0880ac9a755fd29b2688956bd959f933f8' -- sendingAssetId = ETH

union all

select 'Optimism' as block_chain,
    date_trunc('day', block_time) as block_date,
    bytea2numeric_v2(substring(data, 3 + 64 * 7, 64)) as amount, -- amount
    tx_hash
from optimism.logs
where topic1 = '0x438f81f3fe94456cd9d98e9073524f1c2bafb3ce75def8ced69f708061ddd5c4' -- LiFiTransferStarted
    and block_time >= now() - interval '15 days'
    and substring(data, 3 + 64 * 8, 64) = '000000000000000000000000000000000000000000000000000000000000a4b1' -- destinationChainId = 42161
    and ( -- checking for sent ETH/WETH to parse amounts easily
     substring(data, 3 + 64 * 4, 64) = '0000000000000000000000000000000000000000000000000000000000000000' -- sendingAssetId = ETH
     or substring(data, 3 + 64 * 4, 64) = '0000000000000000000000004200000000000000000000000000000000000006' -- sendingAssetId = WETH
    )

),

lifi_arbitrum_transactions_summary as ( select 'LI.FI' as bridge, block_date, count(*) as transaction_count, sum(amount) / 1e18 as transaction_amount from lifi_arbitrum_transactions t where block_date >= now() - interval '15 days' group by 1, 2 ),

combined_summary as ( select bridge, block_date, transaction_count, transaction_amount from celer_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from bungee_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from across_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from multichain_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from hop_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from composable_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from connext_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from hashflow_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from boringdao_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from debridge_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from degate_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from router_arbitrum_transactions_summary a

union

select bridge, block_date,
    transaction_count,
    transaction_amount
from lifi_arbitrum_transactions_summary a
order by block_date, bridge

)

select bridge, date_format(block_date, 'yyyy-MM-dd') as block_date, transaction_count, transaction_amount, sum(transaction_count) over (partition by bridge order by block_date) as accumulate_transaction_count, sum(transaction_amount) over (partition by bridge order by block_date) as accumulate_transaction_amount from combined_summary order by block_date, bridge

Clone this wiki locally