An Introduction to MinaExplorer's BigQuery Public Dataset

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

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
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

  • 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

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

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Sell stocks and understand DP&greedy

Convert CSV to JSON with Python

Organize your RoR project with Docker

A Practical Introduction to Docker Compose

Passing CLI arguments to excutables with ‘go run’

All about Uber She++ India (2021)

Socket Programming in Golang: TCP Clients

Discovering Kotlin Contracts

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

More from Medium

Celonis and BigQuery Integration

Big Query dan Cloud SQL

Big Query dan Cloud SQL

Using Apache Beam to automate your Preprocessing in Data Science

Getting started with Sqoop on Google Cloud Platform