This content originally appeared on Level Up Coding - Medium and was authored by Vivien Chua
One of the chief characteristics of Bitcoin is its limited coin supply. The maximum supply, or the total number of bitcoins that will ever be mined, is capped at 21 million. As of January 2023, 19.2 million bitcoins have been issued, with roughly 1.8 million bitcoins yet to be released. The final bitcoin will only be minted around the year 2140.
Although the number of bitcoins that can be created is limited to 21 million, the number of bitcoins in circulation is likely to be significantly lower. It may be difficult to determine tokens that have been destroyed, misplaced, or simply forgotten and those that are still in the possession of long-term investors. In addition, bitcoin owners risk losing access to their coins if they misplace their wallets’ private keys or if they pass away without disclosing their wallet credentials.
We are also interested in the inflation rate. Inflation is the number of new coins that will be issued over time. Some cryptocurrencies are inherently inflationary, which means that the total number of coins in circulation grows over time. Inflationary cryptocurrencies have declining purchasing power over time due to increases in supply. On the other hand, deflationary cryptocurrencies increase in intrinsic value over time as total supply remains constant or decreases.
Bitcoin is inflationary to a point. Although there is a hard cap in place, the protocol also includes disinflationary mechanisms, which reduce the rate of inflation over time. As bitcoin’s current supply is only 19.2 million while its maximum supply is 21 million, the bitcoin supply will remain slightly inflationary until all 21 million coins have been mined. Also, the mining reward is halved every four years which reduces the inflation rate. Investors believe the halving has catalyzed bitcoin’s legendary bull runs every four years.
In this article, we will use Google BigQuery to query the issuance, circulating supply and inflation rate for Bitcoin.
Issuance
Issuance is the total amount of new coins added to the current supply, i.e. minted coins or new coins released to the network.
- JOIN — Merges the data from two tables — ‘crypto_bitcoin.transactions’ and ‘crypto_bitcoin.outputs’, where ‘tx.hash = outputs.transaction_hash’ to form a single source.
- is_coinbase IS TRUE — Identifies the first transaction added to the block and refers to the block reward for the mining effort
- SUM(rewards) — Returns the sum of rewards on each date
WITH miner_rewards AS (
SELECT DATE(tx.block_timestamp) AS date, tx.is_coinbase, tx.hash,
outputs.transaction_hash, (outputs.value) / 100000000 AS rewards,
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS tx
JOIN `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
ON tx.hash = outputs.transaction_hash
WHERE tx.is_coinbase IS TRUE
),
miner_rewards_grouped_by_date AS (
SELECT date, SUM(rewards) AS issuance
FROM miner_rewards
WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY date
)
SELECT * FROM miner_rewards_grouped_by_date
ORDER BY date
Circulating Supply
Circulating supply is the total amount of all coins ever created/issued.
- SUM(issuance) OVER (ORDER BY date) — Analytic function that returns running total of issuance
- ORDER BY date — Determines order of running total calculation
WITH miner_rewards AS (
SELECT DATE(tx.block_timestamp) AS date, tx.is_coinbase, tx.hash,
outputs.transaction_hash, (outputs.value) / 100000000 AS rewards,
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS tx
JOIN `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
ON tx.hash = outputs.transaction_hash
WHERE tx.is_coinbase IS TRUE
),
miner_rewards_grouped_by_date AS (
SELECT date, SUM(rewards) AS issuance
FROM miner_rewards
WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY date
)
SELECT date, SUM(issuance) OVER (ORDER BY date) AS circulating_supply
FROM miner_rewards_grouped_by_date
ORDER BY date
Inflation Rate (Daily)
Inflation Rate (Daily) is the percentage of new coins issued daily, divided by the current supply.
- SAFE_DIVIDE(issuance, circulating supply) — Returns the result of dividing issuance by circulating supply, and handles division-by-zero errors gracefully.
- SAFE_MULTIPLY(…,100) — Multiplies value by 100 to convert to a percentage
The annual inflation rate is calculated by multiplying the daily inflation rate by 365.
WITH miner_rewards AS (
SELECT DATE(tx.block_timestamp) AS date, tx.is_coinbase, tx.hash,
outputs.transaction_hash, (outputs.value) / 100000000 AS rewards,
FROM `bigquery-public-data.crypto_bitcoin.transactions` AS tx
JOIN `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
ON tx.hash = outputs.transaction_hash
WHERE tx.is_coinbase IS TRUE
),
miner_rewards_grouped_by_date AS (
SELECT date, SUM(rewards) AS issuance
FROM miner_rewards
WHERE date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
GROUP BY date
),
circulating_supply_grouped_by_date AS (
SELECT date, issuance, SUM(issuance) OVER (ORDER BY date) AS circulating_supply
FROM miner_rewards_grouped_by_date
GROUP BY date, issuance
),
inflation_rate_grouped_by_date AS (
SELECT date, SAFE_MULTIPLY(SAFE_DIVIDE(issuance,circulating_supply),100) AS inflation_rate
FROM circulating_supply_grouped_by_date
GROUP BY date, issuance, circulating_supply
)
SELECT * FROM inflation_rate_grouped_by_date
ORDER BY date
Conclusion
Understanding bitcoin’s supply is crucial for determining metrics such as inflation rate and market capitalization. The more assets there are in circulation, the less value they have. This is a reflection of the economic law of supply and demand. By managing their circulating supply, blockchain networks manage the flow of supply and demand, which directly impacts the asset’s price.
Also read:
- Bitcoin: Monitoring Miner Revenue Using BigQuery
- An Easy Way to Access Bitcoin Block Data Using BigQuery
- Query Bitcoin Blockchain for Active Addresses in BigQuery
Thank you for reading!
If you liked the article and would like to see more, consider following me. I post regularly on topics related to on-chain analysis, machine learning and BigQuery. I try to keep my articles simple but precise, providing code, examples and simulations whenever possible.
Decoding Bitcoin Supply Metrics in BigQuery was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.
This content originally appeared on Level Up Coding - Medium and was authored by Vivien Chua
Vivien Chua | Sciencx (2023-02-07T14:44:13+00:00) Decoding Bitcoin Supply Metrics in BigQuery. Retrieved from https://www.scien.cx/2023/02/07/decoding-bitcoin-supply-metrics-in-bigquery/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.