This page provides Blockchain Analytics query examples for Arbitrum.
See the BigQuery documentation for instructions on using BigQuery.
Number of L1 to L2 tickets created in last 30 days
This query shows the number of retryable messages successfully created between Ethereum and Arbitrum in the last 30 days.
In the Google Cloud console, go to the BigQuerypage.
SELECT
DISTINCT
COUNT
(
topics
[
OFFSET
(
1
)])
AS
num_l1_to_l2_tickets_created
FROM
bigquery
-
public
-
data
.
goog_blockchain_arbitrum_one_us
.
logs
WHERE
ARRAY_LENGTH
(
topics
)
>
0
AND
-- "TicketCreated" event emitted via Arbitrum's canonical method for creating L1 to L2 messages.
topics
[
OFFSET
(
0
)]
=
LOWER
(
"0x7c793cced5743dc5f531bbe2bfb5a9fa3f40adef29231e6ab165c08a29e3dd89"
)
AND
block_timestamp
>
(
CURRENT_TIMESTAMP
()
-
INTERVAL
30
DAY
)
;
The following shows an example result:
| num_l1_to_l2_tickets_created |
|---|
| 7779 |
Show withdrawals to L1 (Ethereum), with remaining dispute period
This query shows withdrawals of Eth from Arbitrum to Ethereum using the official Arbitrum bridge , and the remaining estimated dispute period.
In the Google Cloud console, go to the BigQuerypage.
-- UDF for easier string manipulation.
CREATE
TEMP
FUNCTION
ParseSubStr
(
hexStr
STRING
,
startIndex
INT64
,
endIndex
INT64
)
RETURNS
STRING
LANGUAGE
js
AS
r
"""
if (hexStr.length < 1) {
return hexStr;
}
return hexStr.substring(startIndex, endIndex);
"""
;
SELECT
b
.
block_timestamp
as
block_timestamp
,
CONCAT
(
"https://arbiscan.io/tx/"
,
transaction_hash
)
AS
arbiscan_txn
,
(
t
.
value
.
bignumeric_value
/
1000000000000000000
)
AS
eth_withdrawn
,
CONCAT
(
"0x"
,
ParseSubStr
(
t
.
input
,
34
,
LENGTH
(
t
.
input
)))
AS
l1_destination_address
,
CASE
WHEN
DATETIME_DIFF
(
CURRENT_TIMESTAMP
(),
b
.
block_timestamp
,
DAY
)
> =
7
THEN
'True'
ELSE
'False'
END
AS
wait_period_over
,
CASE
WHEN
7
-
DATETIME_DIFF
(
CURRENT_TIMESTAMP
(),
b
.
block_timestamp
,
DAY
)
>
0
THEN
7
-
DATETIME_DIFF
(
CURRENT_TIMESTAMP
(),
b
.
block_timestamp
,
DAY
)
ELSE
0
END
AS
wait_period_days_left
,
FROM
bigquery
-
public
-
data
.
goog_blockchain_arbitrum_one_us
.
transactions
AS
t
INNER
JOIN
bigquery
-
public
-
data
.
goog_blockchain_arbitrum_one_us
.
blocks
AS
b
ON
b
.
block_hash
=
t
.
block_hash
WHERE
t
.
to_address
=
LOWER
(
"0x0000000000000000000000000000000000000064"
)
-- ArbSys contract.
AND
t
.
input
LIKE
"%0x25e16063%"
-- withdrawEth
ORDER
BY
b
.
block_timestamp
DESC
;
| block_timestamp | arbiscan_txn | eth_withdrawn | l1_destination_address | wait_period_over | wait_period_days_left |
|---|---|---|---|---|---|
|
2023-10-02 23:05:26.000000 UTC
|
https://arbiscan.io/tx/0xe6800b17c1b8161fbdf68ea2c0a913c7cc78305da4fbb1b397aa80d524550c95 | 0.013858051475204934 | 0x3a0ab56fb888159eae27f1021a0aa3bd9a73b2be | False | 1 |
|
2023-10-02 22:55:46.000000 UTC
|
https://arbiscan.io/tx/0x4b1695e72b80a9e0a6051f5536af56379da4ed0118364f3089ef554c4e0b2108 | 1.8593161561369025 | 0x8c35933c469406c8899882f5c2119649cd5b617f | False | 1 |
|
2023-10-02 19:51:32.000000 UTC
|
https://arbiscan.io/tx/0x68503fbe4aa013c09e653efaf01586957b9b07a6b3479713c083283eba12b0ac | 0.31 | 0x641763fb275dd3418012ee26591a9898360e9d69 | False | 1 |
|
2023-10-02 17:32:50.000000 UTC
|
https://arbiscan.io/tx/0x683da04b6b4a81c05176128cccbef71ae5d65122d5a52abb65a70107f8101c6c | 0.13967551424971486 | 0x38e69da3f8003c41a7b5e9e5f0a060c6f18215a1 | True | 0 |
