Understanding Bitcoin Transaction Output Types with BigQuery
Bitcoin transactions are recorded in a public ledger called the blockchain. In order to understand Bitcoin transactions, it is important to know about transaction outputs, which are the destinations where bitcoins are sent after a transaction is made. Each transaction has one or more outputs, and these outputs can be of different types. Transaction output types are determined by the type of Bitcoin script conditions that are used to lock Bitcoin in the output.
Each transaction output TxOut has a scriptPubKey field (the locking script), which are script conditions that are used to lock Bitcoin in the output. Each transaction input TxIn references a previous transaction output and includes a scriptSig field (the unlocking script), which is a valid solution for unlocking the bitcoin held in the output by a locking script and allowing it to be spent. Each scriptPubKey/scriptSig pair is validated by the network miners and mined into a block if the script returns true.
Some common transaction output types are:
- P2TR (Pay to Taproot)
- P2WPKH (Pay to Witness Public Key Hash)
- P2WSH (Pay to Witness Script Hash)
- P2SH (Pay to Script Hash)
- P2PKH (Pay to Public Key Hash)
- P2PK (Pay to Public Key)
- Nonstandard
In this article, we will explore the different bitcoin transaction output types and how they work. We will use Google BigQuery, a cloud-based platform for analyzing large datasets, to access and analyze bitcoin transaction data. The dataset for the bitcoin blockchain can be accessed through bigquery-public-data.crypto_bitcoin.outputs as part of the Google Cloud Public Dataset Program.
P2TR (Pay to Taproot)
Pay-to-Taproot (P2TR) is a type of transaction output which locks bitcoin to a script that can be unlocked by a public key, a combination of multiple public keys or a script hash.
This query calculates the number of bitcoin transaction outputs of type “witness_v1_taproot” that occurred each day. Thus, providing insights into the adoption and usage of P2TR over time.
The first Common Table Expressions (CTE) outputs_typeextracts the output type and the date of each transaction output.
The second CTE withnesstaproot_type filters outputs_type to only include output type “witness_v1_taproot” using
WHERE type = “witness_v1_taproot”
The third CTE witnesstaproot_grouped_by_date groups the filtered data by date and counts the number of outputs that occurred on each date using
COUNT(type)— Returns the number of distinct values of type
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
witnesstaproot_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "witness_v1_taproot"
),
witnesstaproot_grouped_by_date as (
SELECT date, COUNT(type) as witnesstaproot_count
FROM witnesstaproot_type
GROUP BY date
ORDER BY date
)
SELECT date, witnesstaproot_count FROM witnesstaproot_grouped_by_dateP2WPKH (Pay to Witness Public Key Hash)
Pay-to-Witness-Public-Key-Hash (P2WPKH) is a type of ScriptPubKey which locks bitcoin to a SegWit address. Also known as the SegWit version of P2PKH. A P2WPKH transaction is similar to a P2PKH transaction in that it still locks bitcoin to the hash of a public key, with the exception that P2WPKH uses SegWit.
This means that the script that unlocks the bitcoin ScriptSig is moved outside of the transaction body and into the Witness section (the WitnessScript). This data is still recorded on the blockchain, but it incurs a lower fee than regular data, making SegWit transactions less expensive.
This query calculates the number of bitcoin transaction outputs of type “pubkeyhash” per day. The dataset is filtered with
WHERE type = “pubkeyhash”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
pubkeyhash_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "pubkeyhash"
),
pubkeyhash_grouped_by_date as (
SELECT date, COUNT(type) as pubkeyhash_count
FROM pubkeyhash_type
GROUP BY date
ORDER BY date
)
SELECT date, pubkeyhash_count FROM pubkeyhash_grouped_by_dateP2WSH (Pay to Witness Script Hash)
Pay-to-Witness-Script-Hash (P2WSH) is a type of ScriptPubKey which locks bitcoin to a SegWit script hash. P2WSH is similar to P2SH transactions in most ways, except that it uses SegWit. The ScriptSig is moved to the Witness section and called the WitnessScript.
This query calculates the number of bitcoin transaction outputs of type “witness_v0_scripthash” per day. The dataset is filtered with
WHERE type = “witness_v0_scripthash”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
witnessscripthash_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "witness_v0_scripthash"
),
witnessscripthash_grouped_by_date as (
SELECT date, COUNT(type) as witnessscripthash_count
FROM witnessscripthash_type
GROUP BY date
ORDER BY date
)
SELECT date, witnessscripthash_count FROM witnessscripthash_grouped_by_dateP2SH (Pay to Script Hash)
Pay-to-Script-Hash (P2SH) is a type of ScriptPubKey which locks bitcoin locks bitcoin to the hash of a script. P2SH was introduced in 2012 as a powerful new type of transaction that greatly simplifies the use of complex transaction scripts.
This query calculates the number of bitcoin transaction outputs of type “scripthash” per day. The dataset is filtered with
WHERE type = “scripthash”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
scripthash_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "scripthash"
),
scripthash_grouped_by_date as (
SELECT date, COUNT(type) as scripthash_count
FROM scripthash_type
GROUP BY date
ORDER BY date
)
SELECT date, scripthash_count FROM scripthash_grouped_by_dateP2PKH (Pay to Public Key Hash)
Pay-to-Public-Key-Hash (P2PKH) is a type of ScriptPubKey which locks bitcoin to the hash of a public key (known as bitcoin address). P2PKH is the successor of P2PK and is the most common script type.
P2PKH are similar to P2PK transactions, except that the bitcoin is locked to the hash of the public key rather than the public key itself. Satoshi developed P2PKH to make bitcoin addresses shorter and more convenient for sending and receiving bitcoins.
This query calculates the number of bitcoin transaction outputs of type “pubkeyhash” per day. The dataset is filtered with
WHERE type = “pubkeyhash”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
pubkeyhash_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "pubkeyhash"
),
pubkeyhash_grouped_by_date as (
SELECT date, COUNT(type) as pubkeyhash_count
FROM pubkeyhash_type
GROUP BY date
ORDER BY date
)
SELECT date, pubkeyhash_count FROM pubkeyhash_grouped_by_dateP2PK (Pay-to-Public-Key)
Pay-to-Public-Key (P2PK) is the first type of ScriptPubKey which locks bitcoin to a public key. This means that the bitcoin can only be spent by the owner of the private key corresponding to the public key specified in the script.
P2PK is most often seen in coinbase transactions in the earlier blocks of the blockchain. This is due to the fact that the original Bitcoin core miner used P2PK as the block reward when creating new blocks.
This query calculates the number of bitcoin transaction outputs of type “pubkey” per day. The dataset is filtered with
WHERE type = “pubkey”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
pubkey_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "pubkey"
),
pubkey_grouped_by_date as (
SELECT date, COUNT(type) as pubkey_count
FROM pubkey_type
GROUP BY date
ORDER BY date
)
SELECT date, pubkey_count FROM pubkey_grouped_by_dateNonstandard
Nonstandard is a catch-all output type for any outputs whose script instructions do not match any of most widely used output types listed above.
This query calculates the number of bitcoin transaction outputs of type “nonstandard” per day. The dataset is filtered with
WHERE type = “nonstandard”
The full query is shown below.
WITH outputs_type AS (
SELECT DATE(block_timestamp) AS date, outputs.type AS type
FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
),
nonstandard_type as (
SELECT date, type
FROM outputs_type
WHERE date > "2009-01-01" AND date <= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND type = "nonstandard"
),
nonstandard_grouped_by_date as (
SELECT date, COUNT(type) as nonstandard_count
FROM nonstandard_type
GROUP BY date
ORDER BY date
)
SELECT date, nonstandard_count FROM nonstandard_grouped_by_dateFinal Thoughts
The results of the query are shown here.
Pay-to-Public-Key-Hash (P2PKH) is used by wallets when you want to send bitcoins and is the most common form of transaction on the network. Since 2021, Pay-to-Witness-Script-Hash (P2WSH) and Pay-to-Script-Hash (P2SH) have been gaining popularity. Pay-to-Public-Key (P2PK) is the earliest transaction output type and is most often seen in coinbase transactions generated by older mining software.
Also read:
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.
