An Introduction to MinaExplorer's BigQuery Public Dataset

Making your first query

While you can use any GUI that supports BigQuery, such as PopSQL, Tableau, or DataGrip, we will use the BigQuery console to execute our queries for this article. Access the console via https://console.cloud.google.com/bigquery, create a new project if required, and add the data source by selecting Add Data -> Pin a project -> Enter project name and enter minaexplorer.

Pin the project for easy access and to view the schema
Browse the schema of the blocks table
SELECT
canonical,
COUNT(statehash) as total
FROM
minaexplorer.archive.blocks
GROUP BY
canonical
Number of blocks in the database, grouped by canonical status

Querying Block Data

Each block has a corresponding datetime field (UTC), which we can use to, for example, group canonical blocks by day.

SELECT datetime_trunc(datetime, DAY) as day,
COUNT(statehash) as total_blocks
FROM minaexplorer.archive.blocks
WHERE canonical = true
GROUP BY day
ORDER BY total_blocks DESC
Number of canonical blocks produced per day

Querying Transaction Data

For transactions, as well as filtering for the canonical status of the transaction, you should also check that the transaction did not fail. You can do this by adding a WHERE failurereason IS NULL filter as this field is only populated if the transaction fails. Let's find how many transactions have failed and how many of each type:

SELECT failurereason,
COUNT(id) as total
FROM minaexplorer.archive.transactions
WHERE canonical = true
GROUP BY failurereason
Transactions can fail and should filter for failurereason IS NULL
SELECT amount / 1000000000 as sent_amount,
`from`,
`to`,
dateTime,
blockheight
FROM minaexplorer.archive.transactions
WHERE canonical = true
AND failurereason IS NULL
AND datetime >= TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL -7 DAY)
ORDER BY amount DESC
Largest transactions over the last 7 days in MINA

Querying SNARK Data

One major advantage of the MinaExplorer data over the Postgres archive node is the ability to extract individual SNARK information (these are combined to a single fee transfer in the archive node). As with the transaction data, SNARK data is also available, stored as nested JSON, in the blocks table.

SELECT
COUNT(fee) as total_snarks,
MIN(fee) / 1000000000 as minimum_fee,
MAX(fee) / 1000000000 as maximum_fee,
AVG(fee) / 1000000000 as average_fee
FROM
minaexplorer.archive.snarks
WHERE
canonical = true
Maximum, minimum, and average SNARK fees in the canonical chain
SELECT COUNT(fee)            as total_snarks,
MIN(fee) / 1000000000 as minimum_fee,
MAX(fee) / 1000000000 as maximum_fee,
AVG(fee) / 1000000000 as average_fee,
FROM minaexplorer.archive.snarks as s,
(
SELECT blockheight
FROM minaexplorer.archive.blocks
WHERE canonical = true
ORDER BY blockheight DESC
LIMIT 1
) as b
WHERE canonical = true
AND s.blockheight >= (b.blockheight - 100)

Querying Account Data

The following query to determine the number of accounts in the ledger demonstrates how accounts are added to the ledger. Either via:

  • Included in the Genesis ledger (ledger hash jx7buQVWFLsXTtzRgSxbYcT8EYLS8KCZbLrfDcJxMtyy4thw2Ee).
  • Received a transaction with an amount high enough to cover the ledger creation fee.
  • Was included via a fee transfer (snark work or coinbase receiver) with a high enough amount to cover the ledger creation fee.
Accounts in ledger
Number of accounts in the MINA ledger
Calculate the balance of any public key. Will return -1 if the address is not in the ledger.
  • Any balance in the Genesis ledger (or a 1 MINA ledger creation fee).
  • The amount of any outgoing transactions that succeded.
  • Any fees paid for any outgoing transactions.
  • The amount of any incoming transactions received.
  • Any coinbase rewards received.
  • Any fee transfers received (via block production or snark work).
Calculate the unvested slot for any timed public key

Foundation Delegation Analysis

The Foundation delegation program requires calculating the amount to return to the Foundation each epoch from the balance of the pool and the number of blocks produced. While scheduled emails now provide this information, we can independently verify the values in the email using a public key and epoch data using the following query.

SELECT SUM(
CAST(json_extract_scalar(h, '$.amount') as NUMERIC)
) / 1000000000 as amount
FROM minaexplorer.archive.blocks
LEFT JOIN unnest(json_extract_array(transactions.usercommands)) as h
WHERE canonical = true
AND json_extract_scalar(h, '$.to') = "${public_key}"
AND protocolstate.consensusstate.slotsincegenesis >= (${epoch} * 7140) + 3501
AND protocolstate.consensusstate.slotsincegenesis <= ((${epoch} + 1) * 7140) + 3500

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Gareth Davies

Gareth Davies

Technical writer, data wrangler and (former) full stack dev