Queries for Horizon/RPC-like Data
Horizon and RPC both provide API endpoints to retrieve data from the Stellar network. The following example queries retrieve the same data by using Hubble with the added benefit of being able to return historical data.
Accounts
Users interact with the Stellar network through accounts. Everything else in the ledger—assets, offers, trustlines, etc.—are owned by accounts, and accounts must authorize all changes to the ledger through signed transactions.
Learn more about accounts.
General account information and XLM balance
select
account_id
, sequence_number as sequence
, sequence_ledger
, sequence_time
, num_subentries
, home_domain
, last_modified_ledger
, balance
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.accounts`
where true
-- equivalent account_id parameter
and account_id=<account_id>
-- filter to the specific date or date range
and batch_run_date between '2024-01-01' and '2024-01-02'
order by closed_at desc
Non-XLM asset balance information
select
account_id
, asset_code
, asset_issuer
, asset_type
, balance
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.trust_lines`
where true
-- equivalent account_id parameter
and account_id=<account_id>
-- filter to the specific date or date range
and batch_run_date between '2024-01-01' and '2024-01-02'
order by closed_at desc
Account signer information
select
account_id
, signer
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.account_signers`
where true
-- equivalent account_id parameter
and account_id=<account_id>
-- filter to the specific date or date range
and batch_run_date between '2024-01-01' and '2024-01-02'
order by closed_at desc
List all accounts
select
account_id
from `crypto-stellar.crypto_stellar_dbt.accounts_current`
where true
-- limit does not reduce the amount of data BigQuery queries
limit 1000
Retrieve an Account’s Transactions
select
id
, transaction_hash
, ledger_sequence
, operation_count
, successful
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_transactions`
where true
-- equivalent account_id parameter
and account=<account_id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at >= timestamp(current_date)
-- limit does not reduce the amount of data BigQuery queries
limit 1000
Retrieve an Account’s Operations
select
op_account_id
, op_id
, type_string
, ledger_sequence
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
-- equivalent account_id parameter
and op_account_id=<account_id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at >= timestamp(current_date)
-- limit does not reduce the amount of data BigQuery queries
limit 1000
Retrieve an Account’s Payments
select
op_account_id
, transaction_id
, transaction_hash
, op_id
, ledger_sequence
, `from`
, `to`
, asset_code
, asset_issuer
, asset_type
, amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
-- equivalent account_id parameter
and op_account_id=<account_id>
and type in (0, 1, 2, 13, 8) -- create_account, payment, path_payment_strict_recieve, path_payment_strict_send, and account_merge
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at >= timestamp(current_date)
-- limit does not reduce the amount of data BigQuery queries
limit 1000
Retrieve an Account’s Effects
select
eho.op_account_id
, he.operation_id
, eho.type_string
, he.details.asset_code
, he.details.asset_issuer
, he.details.asset_type
, he.details.amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_effects` as he
inner join `crypto-stellar.crypto_stellar_dbt.enriched_history_operations` as eho
on he.operation_id = eho.op_id
and eho.closed_at >= timestamp(current_date)
where true
-- equivalent account_id parameter
and op_account_id=<account_id>
-- highly recommended to provide a date range to reduce amount of data queried
and he.closed_at >= timestamp(current_date)
-- limit does not reduce the amount of data BigQuery queries
limit 1000
Retrieve an Account’s Offers
select
seller_id
, offer_id
, selling_asset_code
, buying_asset_code
, amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.offers_current`
where true
-- equivalent account_id parameter
and seller_id=<account_id>
Retrieve an Account’s Trades
with selling_side as (
select
selling_account_address as account_id
, history_operation_id
, selling_asset_code
, buying_asset_code
, selling_amount as amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
-- equivalent account_id parameter
and selling_account_address=<account_id>
-- highly recommended to provide a date range to reduce amount of data queried
and batch_run_date >= current_date
),
buying_side as (
select
buying_account_address as account_id
, history_operation_id
, selling_asset_code
, buying_asset_code
, buying_amount as amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
-- equivalent account_id parameter
and buying_account_address=<account_id>
-- highly recommended to provide a date range to reduce amount of data queried
and batch_run_date >= current_date
),
union_data as (
select
account_id
, history_operation_id
, selling_asset_code
, buying_asset_code
, amount
from selling_side
union all
select
account_id
, history_operation_id
, selling_asset_code
, buying_asset_code
, amount
from buying_side
)
select * from union_data
Assets
Assets are representations of value issued on the Stellar network. An asset consists of a type, code, and issuer.
Learn more about assets.
List all Assets
with assets as (
select
asset_code
, asset_issuer
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_assets`
where true
-- equivalent asset_code/asset_issuer parameters
and asset_code=<asset code>
and asset_issuer=<asset issuer>
),
accounts as (
select
count(ac.account_id) as num_accounts
, countif(ac.flags=1) as authorized_accounts
, sum(balance) as amount
from assets
join `crypto-stellar.crypto_stellar_dbt.trust_lines_current` as ac
on assets.asset_code = ac.asset_code
and assets.asset_issuer = ac.asset_issuer
where true
),
liquidity_pools as (
select
count(*) as num_liquidity_pools
, sum(lpc.asset_a_amount) as liquidity_pools_amount
from assets
join `crypto-stellar.crypto_stellar_dbt.liquidity_pools_current` as lpc
on assets.asset_code = lpc.asset_a_code
and assets.asset_issuer = lpc.asset_a_issuer
where true
),
current_claimable_balances as (
-- TODO: This should be replaced with the claimable_balances_current table when available
SELECT
cb.balance_id
, cb.asset_code
, cb.asset_issuer
, cb.asset_amount
, cb.deleted
, cb.last_modified_ledger
, row_number() over(partition by cb.balance_id, cb.asset_code, cb.asset_issuer order by last_modified_ledger desc, deleted desc) as rn
FROM assets
join `crypto-stellar.crypto_stellar.claimable_balances` as cb
on assets.asset_code = cb.asset_code
and assets.asset_issuer = cb.asset_issuer
where true
order by last_modified_ledger asc
),
claimable_balances as (
select
count(*) as num_claimable_balances
, sum(asset_amount) as claimable_balances_amount
from current_claimable_balances
where true
and rn = 1
and deleted = false
)
select
*
from assets
join accounts on true
join liquidity_pools on true
join claimable_balances on true
Getting Stellar Asset Contract Information for an Asset
select
count(balance_holder) as num_contracts
, sum(parse_bignumeric(balance)) * .0000001 as contracts_amount
from `crypto-stellar.crypto_stellar_dbt.contract_data_current`
where true
and contract_id = <SAC contract id>
and balance != ""
Claimable Balances
A Claimable Balance represents the transfer of ownership of some amount of an asset. Claimable balances provide a mechanism for setting up a payment which can be claimed in the future. This allows you to make payments to accounts which are currently not able to accept them.
List All Claimable Balances
current_claimable_balances as (
-- TODO: This should be replaced with the claimable_balances_current table when available
select
balance_id
, asset_code
, asset_issuer
, asset_amount
, deleted
, last_modified_ledger
-- please see table schema for more available columns and data
, row_number() over(partition by balance_id, asset_code, asset_issuer order by last_modified_ledger desc, deleted desc) as rn
from `crypto-stellar.crypto_stellar.claimable_balances` as cb
where true
-- equivalent asset_code/asset_issuer parameters
and asset_code=<asset code>
and asset_issuer=<asset issuer>
order by last_modified_ledger asc
)
select
*
from current_claimable_balances
where true
and rn = 1
and deleted = false
-- other filters for sponsor and claimants
--and sponsor = <sponsor address>
--and claimants.destination = <account address>
Retrieve a Claimable Balance
with assets as (
-- Used to filter to a specific asset
select
asset_code
, asset_issuer
from `crypto-stellar.crypto_stellar.history_assets`
where true
-- equivalent asset_code/asset_issuer parameters
and asset_code=<asset code>
and asset_issuer=<asset issuer>
),
current_claimable_balances as (
-- TODO: This should be replaced with the claimable_balances_current table when available
select
cb.balance_id
, cb.asset_code
, cb.asset_issuer
, cb.asset_amount
, cb.deleted
, cb.last_modified_ledger
-- please see table schema for more available columns and data
, row_number() over(partition by cb.balance_id, cb.asset_code, cb.asset_issuer order by last_modified_ledger desc, deleted desc) as rn
from assets
join `crypto-stellar.crypto_stellar.claimable_balances` as cb
on assets.asset_code = cb.asset_code
and assets.asset_issuer = cb.asset_issuer
where true
order by last_modified_ledger asc
)
select
*
from current_claimable_balances
where true
and rn = 1
and deleted = false
and balance_id = <claimable balance id>
Retrieve Related Operations and Transactions
select
op_id
, transaction_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
and balance_id = <claimable_balance_id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Effects
Effects represent specific changes that occur in the ledger as a result of successful operations, but are not necessarily directly reflected in the ledger or history, as transactions and operations are.
List All Effects
select
type
, type_string
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_effects`
where true
-- Optional filtering options
--and operation_id = <operation id>
--and address = <account address>
--and details.liquidity_pool_id = <liquidity pool id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Ledgers
Each ledger stores the state of the network at a point in time and contains all the changes - transactions, operations, effects, etc. - to that state.
Learn more about ledgers.
List All Ledgers
select
sequence
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_ledgers`
where true
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve a Ledger’s Transactions and Operations
select
ledger_sequence
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
and ledger_sequence = <ledger sequence>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve a Ledger’s Payments
select
ledger_sequence
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations` as eho
where true
and type in (0, 1, 2, 8, 13) -- create_account, payment, path_payment_strict_recieve, path_payment_strict_send, and account_merge
and ledger_sequence = <ledger sequence>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Liquidity Pools
Liquidity Pools provide a simple, non-interactive way to trade large amounts of capital and enable high volumes of trading.
List Liquidity Pools
select
liquidity_pool_id
, fee
, trustline_count
, asset_a_code
, asset_a_issuer
, asset_a_amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.liquidity_pools_current`
where true
Retrieve a Liquidity Pool
select
liquidity_pool_id
, fee
, trustline_count
, asset_a_code
, asset_a_issuer
, asset_a_amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.liquidity_pools_current`
where true
and liquidity_pool_id = <liquidity_pool_id>
Retrieve a Liquidity Pool’s Transactions and Operations
select
op_id
, transaction_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
and liquidity_pool_id = <liquidity_pool_id>
Retrieve a Liquidity Pool’s Related Trades
select
selling_liquidity_pool_id
, selling_amount
, buying_amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
and selling_liquidity_pool_id = <liquidity pool id>
Offers
Offers are statements about how much of an asset an account wants to buy or sell.
Learn more about offers.
List All Offers
select
offer_id
, seller_id
, selling_asset_code
, selling_asset_issuer
, amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.offers_current`
where true
-- optional filter by account
--and seller_id = <account address>
and closed_at between <start> and <end>
Retrieve an Offer
select
offer_id
, seller_id
, selling_asset_code
, selling_asset_issuer
, amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.offers_current`
where true
and offer_id = <offer id>
and closed_at between <start> and <end>
Retrieve an Offer’s Trades
select
selling_offer_id
, selling_amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
and selling_offer_id = <offer_id>
-- offer_id could also be the buying_offer_id
--and buying_offer_id = <offer_id>
Operations
Operations are objects that represent a desired change to the ledger: payments, offers to exchange currency, changes made to account options, etc. Operations are submitted to the Stellar network grouped in a Transaction.
Each of Stellar’s operations have a unique operation object.
List All Operations
select
op_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
-- optional filters
--and op_source_account = <account address>
--and ledger_sequence = <ledger sequence>
--and liquidity_pool_id = <liquidity pool id>
--and transaction_hash = <transaction hash>
--and transaction_id = <transaction id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
List All Payments
select
op_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations` as eho
where true
-- optional filters
--and op_source_account = <account address>
--and ledger_sequence = <ledger sequence>
--and liquidity_pool_id = <liquidity pool id>
--and transaction_hash = <transaction hash>
--and transaction_id = <transaction id>
and type in (0, 1, 2, 8, 13) -- create_account, payment, path_payment_strict_recieve, path_payment_strict_send, and account_merge
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve an Operation
select
op_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations` as eho
where true
-- op_id = <operation id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve an Operation’s Effects
select
operation_id
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stella.history_effects`
where true
operation_id = <operation id>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Trades
When an offer is fully or partially fulfilled, a trade happens. Trades can also be caused by successful path payments, because path payments involve fulfilling offers.
A trade occurs between two parties—base and counter. Which is which is either arbitrary or determined by the calling query.
Learn more about trades.
List All Trades
select
-- base_asset == selling_asset
-- counter_asset == buying_asset
selling_asset_code
, selling_asset_issuer
, selling_amount
, buying_asset_code
, buying_asset_issuer
, buying_amount
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
-- optional filter columns
and selling_offer_id = <offer id>
and buying_offer_id = <offer id>
and selling_asset_code = <asset code>
and selling_asset_issuer = <asset issuer>
and selling_liquidity_pool_id = <liquidity pool id>
and selling_account_address = <account address>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Transactions
Transactions are commands that modify the ledger state and consist of one or more operations.
Learn more about transactions.
List All Transactions
select
id
, max_fee
, operation_count
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_transactions`
where true
-- optional filters
--and account = <account address>
--and ledger_sequence = <ledger sequence>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve a Transaction
select
id
, max_fee
, operation_count
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_transactions`
where true
and id = <transaction id>
-- transaction_hash is also available
--and transaction_hash = <transaction hash>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Retrieve a Transaction’s Operations
select
transaction_id
, op_id
, max_fee
, operation_count
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
and transaction_id = <transaction id>
-- transaction_hash is also available
--and transaction_hash = <transaction hash>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
Trade Aggregations
A trade aggregation represents aggregated statistics on an asset pair (base and counter) for a specific time period. Trade aggregations are useful to developers of trading clients and provide historical trade data.
List Trade Aggregations
select
date(closed_at)
, selling_asset_code
, selling_asset_issuer
, buying_asset_code
, buying_asset_issuer
, count(1) as number_of_trades
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_trades`
where true
-- there are various columns to filter by
-- and selling_asset_code = <asset code>
-- and selling_asset_issuer = <asset issuer>
-- and buying_asset_code = <asset code>
-- and buying_asset_issuer = <asset issuer>
-- and closed_at between <start> and <end>
group by 1,2,3,4,5
Fee Stats
Fee stats are used to predict what fee to set for a transaction before submitting it to the network.
Retrieve Fee Stats
select
day_agg
, fee_charged_max
, fee_charged_min
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.fee_stats_agg`
where true
and day_agg between <start> and <end>
getEvents
Contract events and diagnostic events are used for supplemental information related to a contract invocation
select
contract_id
, topics_decoded
, data_decoded
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar.history_contract_events`
where true
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>
getLedgerEntries
Enables the retrieval of various ledger states, such as accounts, trustlines, offers, data, claimable balances, and liquidity pools. It also provides direct access to inspect a contract's current state, its code, or any other ledger entry
Contract Data Entries
select
ledger_key_hash
, contract_id
, balance_holder
, balance
from `crypto-stellar.crypto_stellar_dbt.contract_data_current`
where true
and ledger_key_hash = <ledger_key_hash>
Contract Code Entries
select
ledger_key_hash
, contract_code_hash
from `crypto-stellar.crypto_stellar_dbt.contract_code_current`
where true
and ledger_key_hash = <ledger_key_hash>
getTransaction
Transactions are commands that modify the ledger state and consist of one or more operations.
Learn more about transactions.
select
transaction_id
, op_id
, max_fee
, operation_count
-- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.enriched_history_operations`
where true
and transaction_id = <transaction id>
-- transaction_hash is also available
--and transaction_hash = <transaction hash>
-- highly recommended to provide a date range to reduce amount of data queried
and closed_at between <start> and <end>