Queries for Horizon/RPC-like Data - Stellar Developer Docs

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

horizon/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

horizon/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

horizon/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>

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

horizon/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

horizon/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

horizon/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>

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

horizon/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

horizon/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

horizon/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

horizon/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

horizon/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

horizon/fee-stats Fee stats are used to predict what fee to set for a transaction before submitting it to the network. Two models are available: ledger_fee_stats_agg (ledger-grain) and daily_fee_stats_agg (daily-grain). Both provide separate Classic and Soroban fee breakdowns, inclusion fee metrics, and surge pricing statistics.

Retrieve Daily Fee Stats

select
  day_agg
  , total_fee_charged
  , max_fee_charged
  , txn_count
  , failed_txn_count
  , classic_txn_count
  , classic_sum_fee_charged
  , classic_max_fee_charged
  , classic_pct_ledgers_in_surge
  , soroban_txn_count
  , soroban_sum_fee_charged
  , soroban_max_fee_charged
  , soroban_sum_inclusion_fee_charged
  , soroban_sum_resource_fee
  , soroban_pct_ledgers_in_surge
  -- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.daily_fee_stats_agg`
where true
  and day_agg between <start> and <end>

Retrieve Ledger-Level Fee Stats

select
  day_agg
  , ledger_sequence
  , total_fee_charged
  , max_fee_charged
  , txn_count
  , classic_txn_count
  , classic_sum_fee_charged
  , classic_is_surge_ledger
  , soroban_txn_count
  , soroban_sum_fee_charged
  , soroban_sum_inclusion_fee_charged
  , soroban_sum_resource_fee
  , soroban_is_surge_ledger
  -- please see table schema for more available columns and data
from `crypto-stellar.crypto_stellar_dbt.ledger_fee_stats_agg`
where true
  and day_agg between <start> and <end>
  -- optional filter by ledger
  --and ledger_sequence = <ledger sequence>

getEvents

rpc/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

rpc/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

rpc/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>

​Accounts

​General account information and XLM balance

​Non-XLM asset balance information

​Account signer information

​List all accounts

​Retrieve an Account’s Transactions

​Retrieve an Account’s Operations

​Retrieve an Account’s Payments

​Retrieve an Account’s Effects

​Retrieve an Account’s Offers

​Retrieve an Account’s Trades

​Assets

​List all Assets

​Getting Stellar Asset Contract Information for an Asset

​Claimable Balances

​List All Claimable Balances

​Retrieve a Claimable Balance

​Retrieve Related Operations and Transactions

​Effects

​List All Effects

​Ledgers

​List All Ledgers

​Retrieve a Ledger’s Transactions and Operations

​Retrieve a Ledger’s Payments

​Liquidity Pools

​List Liquidity Pools

​Retrieve a Liquidity Pool

​Retrieve a Liquidity Pool’s Transactions and Operations

​Retrieve a Liquidity Pool’s Related Trades

​Offers

​List All Offers

​Retrieve an Offer

​Retrieve an Offer’s Trades

​Operations

​List All Operations

​List All Payments

​Retrieve an Operation

​Retrieve an Operation’s Effects

​Trades

​List All Trades

​Transactions

​List All Transactions

​Retrieve a Transaction

​Retrieve a Transaction’s Operations

​Trade Aggregations

​List Trade Aggregations

​Fee Stats

​Retrieve Daily Fee Stats

​Retrieve Ledger-Level Fee Stats

​getEvents

​getLedgerEntries

​Contract Data Entries

​Contract Code Entries

​getTransaction