Trades - Allium Documentation Hub

Table Details

Property	Value
Table Name	`bitcoin.nfts.trades`
Table Status	Production-Ready
Unique Key	`block_timestamp::date`, `transaction_hash`, `unique_id`
Clustering Key(s)	`block_timestamp::date, marketplace`
Search Optimization	`transaction_hash`, `inscription_id`, `buyer_address`, `seller_address`, `token_tick`, `unique_id`

Bitcoin NFT Trades

The Bitcoin NFT landscape comprises multiple marketplaces. The table below shows the Bitcoin NFT marketplaces indexed by Allium.

Marketplace
ordwallet
ordswap
openordex
magiceden
gamma
unisat
okx

Sample Query

Get the market overview of Bitcoin NFTs, in 10 lines of SQL.

SELECT
    date(block_timestamp) as date,
    marketplace,
    count(*) as "Count",
    count(distinct collection_name) as "Collections",
    count(distinct buyer_address) as "Buyers",
    sum(usd_price) as "USD Volume"
FROM bitcoin.nfts.trades
group by 1,2
order by 1 desc

Get and overview of Bitcoin NFT volumes across key NFT marketplace.

Table Columns

Column Name	Data Type	Description
marketplace	VARCHAR(134217728)	Name of the NFT marketplace where this trade occurred (e.g. `opensea`, `blur`, `x2y2`, `looksrare`, `rarible`).
protocol	VARCHAR(134217728)	Specific version or variant of the protocol (e.g. `uniswap_v3`, `aave_v2`, `stargate_v2`). Qualifies the `project` field with version information.
trade_type	VARCHAR(134217728)	Scope of the trade. `SINGLE_TRADE` for a single NFT, `BUNDLE_TRADE` when multiple NFTs are sold together in one transaction.
order_match_type	VARCHAR(134217728)	How the order was fulfilled. `BUY` for a direct purchase at list price; `ACCEPT_BID` for a seller accepting an existing bid.
buyer_address	VARCHAR(130)	Address of the buyer in this NFT trade.
seller_address	VARCHAR(130)	Address of the seller in this NFT trade.
_nft_recipient	VARCHAR(130)	Internal column tracking the final NFT recipient address after all transfer hops in a trade settlement.
_sales_proceeds_recipient	VARCHAR(130)	Internal column tracking the address that ultimately received the sales proceeds (may differ from seller if royalties or splits apply).
currency_address	VARCHAR(42)	Contract address of the currency used to pay for the NFT. Native currency (e.g. ETH) is represented as the zero address.
currency_symbol	VARCHAR(134217728)	Symbol of the currency used to pay for the NFT (e.g. `ETH`, `WETH`, `USDC`).
raw_gas_fee	VARCHAR(134217728)	Raw miner fee paid for this transaction in satoshis (pre-decimal normalization).
gas_fee	FLOAT	Miner fee paid for this transaction in BTC.
usd_gas_fee	FLOAT	USD value of the miner fee paid for this transaction.
raw_sales_proceeds	VARCHAR(134217728)	Sales proceeds in the payment token’s smallest unit (not decimal-adjusted).
sales_proceeds	FLOAT	Sales proceeds decimal-adjusted to the payment token’s human-readable unit.
usd_sales_proceeds	FLOAT	USD value of the sales proceeds at the time of the trade.
raw_price	VARCHAR(134217728)	Price paid for the NFT in the currency’s smallest unit (not decimal-adjusted).
price	FLOAT	Price per unit of the asset at the time of this event, in USD or in the quote token’s units depending on context.
usd_price	FLOAT	USD value of the NFT trade at the time of the transaction.
buyer_fees	VARIANT	The fees paid by the buyer.
seller_fees	VARIANT	The fees paid by the seller.
agg_fees	VARIANT	The aggregate fees of the trade.
extra_fields	VARIANT	Protocol-specific fields. v2 carries USD fee components (gross_position_fee_usd, borrowing_fee_usd, funding_fee_usd, liquidation_fee_usd, ui_fee_usd, trader_discount_usd, total_cost_usd), price impact, and order/position keys. v1 carries the liquidation fee-token breakdown (fee_token_address, fee_token_symbol, fee_token_amount, fee_token_amount_raw, fee_event_log_index).
raw_fee	VARCHAR(134217728)	Raw marketplace or protocol fee in satoshis.
fee	FLOAT	Fee tier of the liquidity pool, expressed in hundredths of a basis point (e.g. 3000 = 0.3%). Paid by traders to liquidity providers on each swap.
usd_fee	FLOAT	USD value of the marketplace or protocol fee.
item_quantity	VARCHAR(134217728)	Number of NFT items transferred in this trade. Always 1 for ERC-721; may be greater than 1 for ERC-1155 bundle trades.
inscription_id	VARCHAR(73)	ID of the inscription.
inscription_name	VARCHAR(134217728)	Human-readable name of the inscription (e.g. for Ordinals or similar protocols).
inscription_number	VARCHAR(134217728)	Ordinal inscription number assigned at time of inscription. Lower numbers are earlier inscriptions.
collection_name	VARCHAR(134217728)	Name of the collection
collection_slug	VARCHAR(134217728)	Canonical slug identifier for the NFT collection (e.g. noderunners, bitcoin-frogs).
content_type	VARCHAR(134217728)	MIME type of the inscription content (e.g. `text/plain`, `image/png`, `application/json`).
content_tag	VARCHAR(134217728)	High-level content category tag for the inscription (e.g. `image`, `text`, `brc20`, `domain`).
content_length	NUMBER(38,0)	Byte length of the inscription content.
content_protocol	VARCHAR(134217728)	Protocol under which this inscription was created (e.g. `ordinals`, `brc20`, `runes`).
content_parsed	VARIANT	Parsed content of the inscription as a JSON object, for structured protocols like BRC-20 or domain registrations.
is_brc20	BOOLEAN	True if this inscription follows the BRC-20 fungible token standard.
is_token	BOOLEAN	True if this inscription represents a fungible token (BRC-20 or similar).
token_tick	VARCHAR(134217728)	Ticker symbol of the BRC-20 token inscribed (e.g. `ordi`, `sats`).
token_amt	VARCHAR(134217728)	Amount of BRC-20 tokens transferred or minted in this inscription operation.
sats_per_token_tick	VARCHAR(134217728)	Price in satoshis per unit of this BRC-20 token at the time of the trade.
usd_per_token_tick	FLOAT	USD price per unit of this BRC-20 token at the time of the trade.
domain_protocol	VARCHAR(134217728)	Protocol under which this domain name was registered (e.g. `ens`, `.sats`, `.bitmap`).
domain_name	VARCHAR(134217728)	The domain name string registered in this inscription.
domain_namespace	VARCHAR(134217728)	Top-level namespace of the domain (e.g. `.eth`, `.sats`, `.bitmap`).
domain_valid	BOOLEAN	True if this domain registration is valid per the protocol’s rules.
domain_meta	VARCHAR(134217728)	JSON metadata attached to this domain registration (resolver, TTL, records).
is_domain	BOOLEAN	True if this inscription is a domain name registration.
sat_types	VARIANT	Array of satoshi type classifications for the sats in this UTXO (e.g. `uncommon`, `rare`, `epic`).
sat_types_amounts	VARIANT	Array of amounts corresponding to each sat type in `sat_types`.
is_rune	BOOLEAN	True if this transaction involves a Runes protocol transfer or etching.
rune_id	VARCHAR(134217728)	Canonical identifier for a Rune, formatted as : (e.g. 840000:3).
rune_symbol	VARCHAR(134217728)	Unicode symbol character assigned to this Rune at etching time.
spaced_rune	VARCHAR(134217728)	Human-readable Rune name with spacers inserted at configured positions (e.g. UNCOMMON•GOODS).
raw_rune_amount	VARCHAR(134217728)	Amount of Runes transferred, in the Rune’s smallest divisible unit.
rune_amount	FLOAT	Rune amount decimal-adjusted by the Rune’s divisibility.
sats_per_rune	VARCHAR(134217728)	Price in satoshis per unit of this Rune at the time of trade.
usd_per_rune	FLOAT	USD price per unit of this Rune at the time of trade.
rune_number	VARCHAR(134217728)	Sequential integer assigned to this Rune in the order it was etched, starting from 0.
etching_block_timestamp	TIMESTAMP_NTZ(9)	Timestamp of the block in which this Rune was etched (created).
genesis_fee	NUMBER(38,0)	Fee paid in satoshis to etch (create) this Rune or inscription.
satoshi_timestamp	TIMESTAMP_NTZ(9)	Timestamp encoded in the satoshi’s ordinal number, representing when this sat was mined.
transaction_hash	VARCHAR(66)	Hash of the transaction that produced this record. Uniquely identifies a transaction within the blockchain.
transaction_index	NUMBER(38,0)	Zero-based position of the transaction within its block. The first transaction in a block has index 0.
block_timestamp	TIMESTAMP_NTZ(9)	Timestamp (UTC) of the block that contains this record.
block_number	NUMBER(38,0)	Sequential number of the block that contains this record. Starts at 0 (genesis block) and increments by 1 for each new block.
block_hash	VARCHAR(66)	Cryptographic hash of the block header that contains this record. Uniquely identifies a block.
unique_id	VARCHAR(134217728)	Allium’s deterministic unique identifier for this row. Generated from the fields that uniquely identify the record (e.g. transaction hash + log index). Stable across full refreshes.
_metadata_updated_at	TIMESTAMP_NTZ(9)	When the stablecoin metadata was last updated.
_created_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was created in the database.
_updated_at	TIMESTAMP_NTZ(9)	Timestamp of when the entry was last updated in the database.
_changed_since_full_refresh	BOOLEAN	Indicates if the record has changed since the last full data refresh.