Links

SQL Tables

The following SQL workspaces and tables can be queried in Sort

Workspaces

A workspace is similar to a database and can contain a number of tables. Sort currently has 2 workspaces (1 workspace per blockchain).
Workspace
Description
Sample query
ethereum
Query transactions and transaction logs (events) within Ethereum mainnet. This data is updated every second.
select * from ethereum.transaction
goerli
Query transaction data on the Goerli testnet. This data is updated every second.
select * from goerli.transaction

Tables

Workspace
Table
Description
ethereum
Every decoded Ethereum transaction for the past 7 days. Function name and parameters are also included.
ethereum
All decoded logs for a transaction, including function name, contract address, and all parameters.
goerli
goerli
ethereum
Experimental view to query NFT transactions

transaction

select * from ethereum.transaction t limit 10
select * from goerli.transaction t limit 10
Column Name
Description
Type
_id
transaction hash
string
block_number
block number that contained this transaction e.g. block_number = 15397960
long
confirmations
transactions are stored after 1 confirmation e.g. confirmations = 1
long
decode
should this transaction be decoded (internally used)
boolean
from
address that initiated this transaction, query this fields by use quotes, e.g. t."from"
string
to
destination address
string
contract_name
if "to" field is a contract, contract name e.g. contract_name = 'Tether USD'
string
function
function called as part of this transaction. Any field within the JSON document can be part of the SQL query by specifying the JSON path, e.g. function.name or function.params[1].value e.g. {"name":"transfer","params":[{"value":"0x7360ea47b9fbd37b9f68d1a437f731260cf61aa3","name":"to","type":"address"},{"value":"5830000000","name":"value","type":"uint256"}]}
json document
function.name
name of function e.g. function.name = 'transfer'
string
function.params[*].name
name of parameter passed into function e.g. function.params[2].name = 'to'
string
function.params[*].type
type of parameter passed into function e.g. function.params[2].name = 'uint256'
string
function.params[*].value
value of parameter passed into function e.g. function.params[1].value = '0x7360ea47b9fbd37b9f68d1a437f731260cf61aa3'
*
gas
gas related values for transaction
json document
gas.max_fee_per_gas.gwei
gas.transaction_fee.eth
gas.used
input
encoded input for the function
string
nonce
integer
timestamp
timestamp of block that contained transaction
datetime
value
value of transaction in wei
string
value_eth
value of transaction in eth
long
price_eth___usd
Daily price of ETH in USD on the day this transaction was confirmed
float

transaction_log

// simple query
select * from ethereum.transaction_log l limit 10
// query goerli testnet
select * from goerli.transaction_log l limit 10
// join transaction and logs
select * from ethereum.transaction_log l, sort.transaction t
where l.transaction_hash=t._id limit 10
Column Name
Description
Type
_id
transaction hash + log index
string
function_address
address of function call
string
function_contract___name
contract name of function call
string
name
name of function call
string
index
index of log message in transaction
integer
params[*].name
name of parameter passed into function
string
params[*].type
type of parameter passed into function
string
params[*].value
value of parameter passed into function
*
timestamp
timestamp of block that contained transaction
datetime
transaction_hash
transaction hash
string
transaction_value_eth
value of transaction in ETH
number
transaction_to
to address of transaction
string
transaction_from
from address of transaction
string
transaction_function_name
function name of transaction
string
price_eth_usd
Daily price of ETH in USD on the day this transaction was confirmed
float

nft_transaction (view - experimental)

Experimental view to query NFT transactions. This is a view made up of additional views. The idea is to create a view per NFT exchange, and query all views through the nft_transaction. Github repo of all SQL behind the views is here.
// simple query for latest NFT transactions
select * from ethereum.nft_transaction order by timestamp desc
Column Name
Description
Type
nft_name
Contract name of NFT
string
nft_contract_address
Address of NFT contract
string
token_id
Token ID of NFT
string
timestamp
Transaction date
datetime
nfts_in_txn
Number of NFTs in this transaction hash
number
amount_eth
Amount of transaction
number
currency
WETH/ETH
number
amount_usd
Dollar amount of transaction, using the price_eth_usd column
number
exchange
Name of NFT exchange
string
exchange_address
Contract address of NFT exchange
string
transaction_hash
Hash of transaction
string
to
To address, usually the contract of the exchange
string
from
From address of the transaction
string