Google Bigquery

Introduction

Blockchain Analytics offers indexed blockchain data made available through BigQuery for easy analysis through SQL.

Blockchain Analytics offers you access to reliable data without the overhead of operating nodes or developing and maintaining an indexer. You can now query the full history of blocks, transactions, logs and receipts for Cronos.

By leveraging datasets in BigQuery, you can access blockchain data as easily as your internal data. By joining chain data with application data, you can get a complete picture of your users and your business.

How are these datasets different from the existing public dataset?

Like the existing public blockchain datasets, customers are not charged for storage of the data, only for querying the data based on BigQuery pricing.

Quickstart

  1. Go to Cronos dataset and click on one of the samples.

  2. You will get to the console and see the Cronos dataset on the left in the explorer

Please note BigQuery charges are based on the amount of data processed by your queries, so running the query may incur charges to your account. You can find the consumption estimate in the top right corner similar to the warning of "This query will process 65.73 MB when run."

  1. If you see on the sample you should get the BigQuery SQL code to query: Which wallets had the most number of interactions with the Wrapped Cronos contract in the past 30 days?

    Let's click the big RUN button. (To save costs, replace the existing query with the one below, using a "1 day" interval instead of "30 days" in the BigQuery console). To start developing your own BigQuery SQL code, we refer to the following syntax. For the Cronos data schema we refer to the Google Cloud Cronos schema. 

SELECT
 t.from_address AS address,
 CONCAT("https://cronoscan.com/address/", t.from_address) AS cronoscan_link,
 COUNT(t.from_address) AS num_transactions
FROM
 `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions` AS t
INNER JOIN
 bigquery-public-data.goog_blockchain_cronos_mainnet_us.blocks AS b
ON
 b.block_hash = t.block_hash
WHERE
 t.to_address = LOWER("0x5C7F8A570d578ED84E63fdFA7b1eE72dEae1AE23") -- Wrapped CRO
AND
 b.block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR)
AND
 t.block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR)
GROUP BY
 t.from_address
ORDER BY
 COUNT(t.from_address) DESC
;

  1. We can now query the results in the results tab below, further explore by exporting the results or visualizing in another tool such as Google sheets or Looker.

Rowaddresscronoscan_linknum_transactions

1

0x3270c9a4558774cc8f2a19708edc190366028b96

https://cronoscan.com/address/0x3270c9a4558774cc8f2a19708edc190366028b96

1

2

0xbeaf1c7fed452be2dcfd2a8fe1fcd74b241acfc7

https://cronoscan.com/address/0x693fb96fdda3c382fde7f43a622209c3dd028b98

1

3

0xddb162b31f562f1be0fa585d3ca6a55786e59af3

https://cronoscan.com/address/0x6614d26064d762922c7bc7a00337713d5169ae7c

1

Example queries

  1. Latest indexed block

SELECT
  MIN(block_number) AS `First block`,
  MAX(block_number) AS `Newest block`,
  COUNT(1) AS `Total number of blocks`
FROM
  `bigquery-public-data.goog_blockchain_cronos_mainnet_us.blocks` AS t
RowFirst blockNewest blockTotal number of blocks

1

1

12134627

12134627

  1. Daily transactions in the last 10 days


SELECT
  DATE(block_timestamp) AS date,
  COUNT(*) AS num_transactions
FROM
  `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions`
WHERE
  block_timestamp >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 10 DAY)
GROUP BY
  1
ORDER BY
  1 DESC;
Rowdatenum_transactions

1

2024-01-18

10250

2

2024-01-17

47747

3

2024-01-16

49717

4

2024-01-15

47099

5

2024-01-14

47051

6

2024-01-13

43926

7

2024-01-12

50448

8

2024-01-11

60904

9

2024-01-10

61774

10

2024-01-09

54521

11

2024-01-08

44194

  1. View the blocks with largest CRO value transfer in the past hour

SELECT block_hash, SUM(value.bignumeric_value / 1000000000000000000) value_total
FROM `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions` AS t
 JOIN `bigquery-public-data.goog_blockchain_cronos_mainnet_us.receipts` AS r USING (block_hash, transaction_hash)
WHERE status = 1
AND
 t.block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR)
AND
 r.block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR)
GROUP BY block_hash
ORDER BY value_total DESC
LIMIT 5

Row

block_hash

value_total

1

0x7cac0bbf3909902a8670f962fbc9721391178850a2672d82b75c5a79b332a4f8

36836.840925000000000001

2

0xb53bd2c1a1d136e9b4dda4af47c488d278a0ee450adecd991cf13b187ce17a93

36729

3

0x28e0d8c31625ca43b565f1202b91e6cb20b709e25bea65185dcda7a3d176957d

33359.85627

4

0xfe8e732779101854cfaeed2404f7b53d3b64879069b5d8e8372f64d1cfe4a47f

22577.325273500000000015

5

0xd0b81821a57dc939dce80b29e9642f4a39e4bd2d346b5943a2532e69f191de57

22231.469422733370862931

  1. Top 10 wallets by number of transactions in the last hour

SELECT
 from_address,
 COUNT(*) AS num_transactions
FROM `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions` as t
WHERE
t.block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR)
GROUP BY from_address
ORDER BY num_transactions DESC
LIMIT 10;
Rowfrom_addressnum_transactions

1

0x25aa97464f38a1506a16160bbc03cfc6dd863da3

211

2

0x227f6757289a86c13eee2e91c2e6eb03f2ed11a6

136

3

0x95d49a8a2d69b2a2de4a00655d05ee39f9c41108

134

4

0x15d190dd8a1ed39cf5b790e2ffed1f365e9c865b

116

5

0xc9219731adfa70645be14cd5d30507266f2092c5

80

6

0x6614d26064d762922c7bc7a00337713d5169ae7c

65

7

0x34cfa46732692ab062f0453036cd5a4f5b771473

59

8

0x693fb96fdda3c382fde7f43a622209c3dd028b98

50

9

0x71f0cdb17454ad7eeb7e26242292fe0e0189645a

50

10

0x518a9d51ba8841046859a7722e75f92ffdadd0c4

37

  1. All USDT activity in the past hour

-- 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);
""";
-- UDF to convert hex to decimal.
CREATE TEMP FUNCTION HexToDecimal(hexStr STRING)
RETURNS INT64
LANGUAGE js
AS r"""
 return parseInt(hexStr, 16);
""";

SELECT
 t.transaction_hash,
 t.from_address AS from_address,
 CONCAT("0x", ParseSubStr(l.topics[OFFSET(2)], 26, LENGTH(l.topics[OFFSET(2)]))) AS to_address,
 (HexToDecimal(l.data) / 1000000) AS usdt_transfer_amount
FROM
 `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions` AS t
INNER JOIN
 `bigquery-public-data.goog_blockchain_cronos_mainnet_us.logs` AS l
ON
 l.transaction_hash = t.transaction_hash
WHERE
 t.to_address = LOWER("0x66e428c3f67a68878562e79a0234c1f83c208770") -- USDT
AND
 t.block_timestamp >= (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR) 
AND
 l.block_timestamp >= (CURRENT_TIMESTAMP() - INTERVAL 1 HOUR) 
AND
 ARRAY_LENGTH(l.topics) > 0
AND
 -- Transfer(address indexed src, address indexed dst, uint wad)
 l.topics[OFFSET(0)] = LOWER("0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef") -- Transfer
;

Row

transaction_hash

from_address

to_address

usdt_transfer_amount

1

0xa3b78f79dee6970f3abc763b52f24a6d46aeba3e2370943f8eb2d68ff00d788a

0xc9219731adfa70645be14cd5d30507266f2092c5

0x539b85a6853e8740cd918009197c799d205787eb

309.82

2

0xc2abd163669a703ee850e432ac7c1a744b63bb1ff8e9b4cdee3ec2d95768fc75

0xc9219731adfa70645be14cd5d30507266f2092c5

0xc041126c1d07b72ee0f366e1fca339b4fed537cb

9.38

3

0x214cdef8263b4f8cc517d09673c126375c682b9b83d55c7ad889055c57533390

0xc9219731adfa70645be14cd5d30507266f2092c5

0xfca38e2882d8a549c660ccb17a8fe0463fab060e

11.82

4

0x40e24cc93abc746aa7c96482144772421412b9a733210644bab6ff6290996a1e

0xc9219731adfa70645be14cd5d30507266f2092c5

0x67b652172633b451a826aac6da7ed63693133fd2

11.26

5

0xb211dcb87ec8bbb115a4c6eae6c5a8861e04557b8b502e5a5858e70ae512183b

0x539b85a6853e8740cd918009197c799d205787eb

0x8995909dc0960fc9c75b6031d683124a4016825b

309.82

6

0xff2bc840e8dd59bd1f680c6c0905aada50fb4149e298ac0b932e36fd6d44fa34

0xc447ea5dc46ec5f83cdfe17cf0ccf3730ea12036

0x515e9917819f11ab0b6b6e6cee3848a935003bf7

700.0

  1. [DApp] Count the total number of unique transactions and users interaction with a specific smart contract on a given day 

SELECT
 COUNT(DISTINCT transaction_hash) AS total_transactions,
 COUNT(DISTINCT from_address) AS unique_users,date(block_Timestamp) as date
FROM
`bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions`
WHERE
 to_address = '0x9fae23a2700feecd5b93e43fdbc03c76aa7c08a6'
 AND block_timestamp BETWEEN TIMESTAMP('2023-01-01 00:00:00') AND TIMESTAMP('2023-01-02 00:00:00')
GROUP BY
date

Row

total_transactions

unique_users

date

1

288

131

2023-01-01

  1. [DApp] Top 5 addresses with the highest number of transactions sent to the contract at a specific contract within the specified date range

SELECT
 from_address,
 COUNT(transaction_hash) AS transaction_count,
FROM
 `bigquery-public-data.goog_blockchain_cronos_mainnet_us.transactions`
WHERE
 to_address = '0x9fae23a2700feecd5b93e43fdbc03c76aa7c08a6'
 AND block_timestamp BETWEEN TIMESTAMP('2024-05-01 00:00:00 UTC') AND TIMESTAMP('2024-07-31 23:59:59 UTC')
GROUP BY
 from_address
ORDER BY
 transaction_count DESC
LIMIT 5

Row

from_address

transaction_count

1

0x8c0ec5772bd92d55edd1325d022ec07e54ae1b0e

3515

2

0x849c34e2bcd65a861dfaefe415aafb2826c46b96

224

3

0xc9219731adfa70645be14cd5d30507266f2092c5

129

4

0xfe5b12a84019dcd8178a4c684885a8e685bef238

72

5

0x254b5fed7b453831e89e566a2e02678636e9f010

46

PreviousFlairNextMoralis

Last updated