SQL Tables
The following SQL workspaces and tables can be queried in Sort
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 |
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 |
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 |
// 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 |
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 |
Last modified 4mo ago