An Introduction to MinaExplorer's BigQuery Public Dataset

See this post on how to set up and configure an archive node for redundancy.

The schema of the BigQuery dataset matches that of the MinaExplorer GraphQL API. Notably, snarks and user transactions (aka user commands), which are also nested in blocks data, are separated into their own tables for easier querying.

Making your first query

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

While MinaExplorer pays to host the dataset, any queries you run against the dataset are charged against your own personal billing account. There is a generous free tier of 1TB of data processing to get started.

Querying Block Data

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

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

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

Yes, this block really did have a SNARK included for 700 MINA.

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

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.
Calculate the unvested slot for any timed public key

Foundation Delegation Analysis

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