# Google Bigquery (Analytics)

The BigQuery Cronos zkEVM dataset makes it possible for anyone to perform complex queries on transactions, blocks, batches and logs. 

BigQuery datasets are available for Cronos zkEVM and Cronos EVM on [Google's Web3 product page](https://cloud.google.com/application/web3/discover).

By leveraging datasets in BigQuery, you can access blockchain data as easily as your internal data. You can query the full history of blocks, transactions, logs and receipts for these two chains. By joining chain data with application data, you can get a complete picture of your users and your business.

Developers only need a Google Cloud account with BigQuery enabled.

## Quickstart <a href="#quickstart" id="quickstart"></a>

1. [Go to Cronos zkEVM dataset](https://console.cloud.google.com/bigquery/analytics-hub/exchanges/projects/596763852542/locations/us/dataExchanges/cronos_zkevm_1922b84ea5e/listings/cronos_zkevm_mainnet_1922b96033f?project=groovy-footing-429219-v8) and click on one of the [samples](https://console.cloud.google.com/bigquery?sq=593585632926:0715bee89d184ba782c1de10dd01399b).
2. You will get to the console and see the Cronos zkEVM dataset on the left in the explorer.<br>

   <figure><img src="https://3199918912-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuSKiKu5wNi55i329LvV2%2Fuploads%2FtxBQAfzscWHtSbCktEWC%2Fimage.png?alt=media&#x26;token=67eb1bde-f558-421c-ba65-a31426002a90" alt=""><figcaption></figcaption></figure>

{% hint style="warning" %}
**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 1.37 GB when run."
{% endhint %}

&#x20; 3\. You can see below BigQuery SQL query examples to run in console by click "Run". \ <br>

To start developing your own BigQuery SQL code, you can refer to the following [syntax](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax). \
\
For the Cronos data schema we refer to the [Google Cloud Cronos schema](https://cloud.google.com/blockchain-analytics/docs/schema#cronos_mainnet).

## Example Queries

### 1. Show all Veno USD transfers

This query shows transfers of the Veno USD token on Cronos zkEVM since genesis.

In the Google Cloud console, go to the BigQuery page.

The following query is loaded into the Editor field: \
Replace `your-project-id.cronos_zkevm_mainnet` in the following query with the project ID and the linked dataset name you used when you subscribe to this dataset. &#x20;

```sql
-- 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 BIGNUMERIC
LANGUAGE js
AS r"""
  return BigInt(hexStr, 16);
""";

SELECT
  t.hash as transaction_hash,
  t.from_address AS from_address,
  CONCAT("0x", ParseSubStr(l.topic2, 26, LENGTH(l.topic2))) AS to_address,
  (HexToDecimal(l.data) / 1000000000000000000) AS veno_usd_transfer_amount
FROM
  `your-project-id.cronos_zkevm_mainnet.transactions` AS t
INNER JOIN
  `your-project-id.cronos_zkevm_mainnet.logs` AS l
ON
  l.transaction_hash = t.hash
WHERE
  t.to_address = LOWER("0x5b91e29Ae5A71d9052620Acb813d5aC25eC7a4A2") -- Veno USD
AND
  l.topics_count > 0
AND
  -- Transfer(address indexed src, address indexed dst, uint wad)
  l.topic0 = LOWER("0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef")
;
```

The following shows an example result:

| transaction\_hash                                                  | from\_address                              | to\_address                                | veno\_usd\_transfer\_amount |
| ------------------------------------------------------------------ | ------------------------------------------ | ------------------------------------------ | --------------------------- |
| 0x1810569dbf9f188abef8a81f71cedc526dc5826185f901bde77a315836e8bb2e | 0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a | 0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890 | 1.065316917013257903        |
| 0xe3064e01b2d0915373d2134c50bdeb4a0a4bda3e7e3c48c58efe50e84afec0ec | 0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a | 0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890 | 1.06532400320732326         |
| 0xd872a1f2a89d0c5ffdf8348de232aee2359fee0893504567cf382fbe24fa746e | 0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a | 0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890 | 1.06532435751826383         |
| 0xbef982225518fcf3dfc2b705dbd1855dd7895f3b12688b1bfff9f69982cd0458 | 0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a | 0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890 | 1.065326129074734257        |
| 0x851199e970aac6c5a4089edba79eab0c9526a4a9151d4ca27b2494b48cfff3db | 0xf4b5e4e82f425154dc7c2eeaac7cbb3c0b70c43a | 0x2a66ac1167ae3f6c504805f6a72ccddc0d9bb890 | 1.065322585964739367        |

### 2. Wrapped zkCRO activity

This query shows the wallets with the most interactions with Wrapped zkCRO in the last 30 days.

In the Google Cloud console, go to the BigQuery page.

The following query is loaded into the Editor field:

Note: Replace `your-project-id.cronos_zkevm_mainnet` in the following query with the project ID and the linked dataset name you used when you subscribe to this dataset.

```sql
SELECT
  from_address AS address,
  CONCAT("https://explorer.zkevm.cronos.org/address/", from_address) AS explorer_link,
  COUNT(from_address) AS num_transactions
FROM
  `your-project-id.cronos_zkevm_mainnet.transactions` AS t
WHERE
  to_address = LOWER("0xC1bF55EE54E16229d9b369a5502Bfe5fC9F20b6d") -- Wrapped zkCRO
AND
  block_timestamp > (CURRENT_TIMESTAMP() - INTERVAL 30 DAY)
GROUP BY
  from_address
ORDER BY
  COUNT(from_address) DESC
;
```

The following shows an example result:

| address                                    | explorer\_link                                                                         | num\_transactions |
| ------------------------------------------ | -------------------------------------------------------------------------------------- | ----------------- |
| 0x4712633d145d5c5b5b35b95e1a4fb06bbad4b1ec | <https://explorer.zkevm.cronos.org/address/0x4712633d145d5c5b5b35b95e1a4fb06bbad4b1ec> | 59                |
| 0x9d6e06ec5c09b09559d65a3e2906b2b1c2ac0b68 | <https://explorer.zkevm.cronos.org/address/0x9d6e06ec5c09b09559d65a3e2906b2b1c2ac0b68> | 37                |
| 0x06556fe0eb119fcfcc0025ab7427e2c9eafd8e98 | <https://explorer.zkevm.cronos.org/address/0x06556fe0eb119fcfcc0025ab7427e2c9eafd8e98> | 28                |
| 0xed0598b07faacb711870b58eae40878128928a4b | <https://explorer.zkevm.cronos.org/address/0xed0598b07faacb711870b58eae40878128928a4b> | 26                |
| 0x3d5bc4222549f7e74d1da14745ab34dec8b2c90c | <https://explorer.zkevm.cronos.org/address/0x3d5bc4222549f7e74d1da14745ab34dec8b2c90c> | 20                |

### Contract Addresses

You can find a list of key contract addresses on [Cronos zkEVM documentation](https://docs-zkevm.cronos.org/for-developers/contract-addresses).