CryptoIcicleAcross-1.Bridge Transactions (Across)
Updated 2022-06-20
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- Bridge Transactions (Across)
-- Q1. Analyze bridge transactions via the Across bridge over the last two weeks. What assets are users bridging from Ethereum, and to what chains?
-- Payout 75 USDC
-- Grand Prize 225 USDC
-- Level Intermediate
-- Q1. Analyze bridge transactions via the Across bridge over the last two weeks.
-- What assets are users bridging from Ethereum, and to what chains?
-- Across transaction event data is undecoded in the Ethereum core tables.
-- Users can use these functions to parse and decode the data:
-- regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data ethereum.public.udf_hex_to_int()
-- Hint: A user begins a bridge transaction by calling the deposit function on the SpokePool contract from the origin chain.
-- Ethereum SpokePool contract : 0x4D9079Bb4165aeb4084c526a32695dCfd2F77381
with
txns as (
SELECT
tx_hash,
regexp_substr_all(SUBSTR(data, 3, len(data)), '.{64}') AS segmented_data,
ethereum.public.udf_hex_to_int(segmented_data[2]) as chain_id,
case
when chain_id = '1' then 'ethereum'
when chain_id = '137' then 'polygon'
when chain_id = '10' then 'optimism'
when chain_id = '100' then 'gnosis'
when chain_id = '42161' then 'arbitrum'
end as chain_destination
from ethereum.core.fact_event_logs
where chain_destination is not null and block_timestamp >= CURRENT_DATE - 14
and origin_to_address = '0x4d9079bb4165aeb4084c526a32695dcfd2f77381'
),
Run a query to Download Data