Carried out a search on some SQL programs that can query the blockchain, Saw Abe, but it required that I download bitcoin core, which wasn't what I needed. Knew of some RPC commands that people who run full nodes can use to retrieve data on the blockchain. So I checked out bigquery, read it on a
PDF, a google product that has some bitcoin datasets, with two tables blocks and transaction, including interval structures stored in columns; TXin & txout of a transaction, stored in the inputs and outputs columns of the transactions respectively.
It all looked complex to understand at first glance, but read further to discover that it's mainly to help analysis for various kinds of analytic problems, and not for exploring individual transactions.
I queried for the transactions with zero fees, which I also read on the introductory aspect of the program, which explains that miners add their transactions to a block without paying fees. The query retrieved the number of transactions with zero fees
SELECT
ROUND((input_value - output_value)/ size, 0) AS fees_per_byte,
COUNT(*) AS txn_cnt
FROM
`bigquery-public-data.crypto_bitcoin.transactions`
WHERE TRUE
AND block_timestamp >= '2023-08-24'
AND is_coinbase IS FALSE
GROUP BY 1
Did another test run on another query I found on the article written about the bitcoin dataset, the query is to retrieve balances on different addresses.
WITH double_entry_book AS (
-- debits
SELECT
array_to_string(inputs.addresses, ",") as address
, inputs.type
, -inputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
UNION ALL
-- credits
SELECT
array_to_string(outputs.addresses, ",") as address
, outputs.type
, outputs.value as value
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
)
SELECT
address
, type
, sum(value) as balance
FROM double_entry_book
GROUP BY 1,2
ORDER BY balance DESC
LIMIT 1000
This retrieves different addresses with the amount of bitcoin contained in them, but the numbers didn't look realistic at first, like this 5502219754649, then I copied one address:
bc1qjasf9z3h7w3jspkhtgatgpyvvzgpa2wwd2lr0eh5tx44reyn2k7sfc27a4 searched on blockchain explorer and found that the digits doesn't have a decimal point that separates the number, though correct. It looked this way on block explorer 55022.19754649 Tried out different queries, which I may not post because I didn't understand the results, didn't stop loading or too long to share.
The bitcoin dataset is 'bigquery-public-data.crypto_bitcoin' other cryptocurrency datasets also exists too, you can read this
threadGoogle is still building the model at the moment I notice some limitations, you can also use Legacy or Google SQL to execute query on bigquery.
