XRPL tables overview
List of Tables
All our tables that contain XRPL-related data have the string 'xrp' in their name. So one easy way to find them yourself is by running the following query:
1
SHOW TABLES LIKE '%xrp%'
1
2
3
4
5
6
7
8
┌─name───────────────────┐
│ xrp_balances │
│ xrp_balances_shard_v5 │
│ xrp_dex_volume │
│ xrp_dex_volume_shard │
│ xrp_ripple_state │
│ xrp_ripple_state_shard │
└────────────────────────┘
Exploration of the tables
We can inspect the tables with the commands we learnt in Exploration
Below we have listed all the XRPL tables with thier columns and a brief descriptions of them.
xrp_balances
Represents balance changes per address per currency.
- dt (DateTime): The date time of the balance change
- blockNumber (UInt64): Unique identifier for the block in which the transaction was processed
- balance (Float64): The balance of the asset in the account after the transaction was completed
- oldDt (Nullable(DateTime)): The date time of the previous balance change
- oldBlockNumber (Nullable(UInt64)): The block number of the previous balance change
- oldBalance (Float64): The previous balance in this currency for this address
- address (String): The owner of the funds
- currency (String): Type of currency used in the transaction
- issuer (Nullable(String)): The issuer of the currency
- issuerCurrency (String): The type of the issued currency
- addressType (String): Enum(NORMAL | SPECIAL) is the address a valid XRPL address or a meta address used for metric purposes
- transactionIndex (UInt64): Position of the transaction that caused the balance change inside the XRPL block
- transactionHash (String): Hash value identifying the transaction that caused the balance change
xrp_dex_volume
Represents trades on the XRPL DEX.
- dt (DateTime): When the transaction happened.
- blockNumber (UInt64): Unique identifier for the block in which the transaction was processed
- offerAddress (String): The owner of the funds
- makerAddress (String): The address of the account that owns the Offer that resulted in a DEX trade
- offerSequence (UInt32) - The sequence number of the Offer transaction that resulted in a DEX trade
- takerPaysIssuerCurrency (String): The currency type offered by the taker
- takerPaysAmount (Float64): The amount of currency offered by the taker
- takerGetsIssuerCurrency (String): The currency type recieved by the taker
- takerGetsAmount (Float64): The amount of currency recieved by the taker
- transactionIndex (UInt64): Position of the transaction that caused the balance change inside the XRPL block
- transactionHash (String): Hash value identifying the transaction that caused the balance change
- xrpAmount (Nullable(Float64)): The amount of the trade measured in XRP
xrp_ripple_state
Represents creation and destruction or XRPL trustlines.
- sign (Int8): If sign is '1' it denotes creation and if it is '-1' deletion of trustline
- dt (DateTime): When the transaction happened.
- blockNumber (UInt64): Unique identifier for the block in which the transaction was processed
- transactionIndex (UInt32): Position of the transaction that caused the balance change inside the XRPL block
- transactionHash (String): Hash value identifying the transaction that caused the balance change
- currency (String): Type of currency used in the trustline
- balance (Float64): Accumulated balance
- highLimitIssuer (String): Issuer of the high account
- highLimitBalance (Float64): Balance of the high account
- lowLimitIssuer (String): Issuer of the low account
- lowLimitBalance (Float64): Balance of the low account
Sample Queries
DEX volume per asset pair, measured in XRP
- DEX volume for a specific asset pair, per day, measured in XRP
1 2 3 4 5 6 7 8 9
WITH ('r3kmLJN5D28dHuH8vZNUZpMC43pEHpaocV/USD', 'rvYAfWj5gh67oV6fW32ZzP3Aw4Eubs59B/BTC') AS issuer_pair SELECT toDate(dt) AS day, SUM(xrpAmount) AS count_final FROM xrp_dex_volume WHERE (takerPaysIssuerCurrency IN (issuer_pair)) OR (takerGetsIssuerCurrency IN (issuer_pair)) GROUP BY day ORDER BY day DESC LIMIT 100
Test in Queries
DEX volume per asset pair, measured in USD
- DEX volume for a specific asset pair, per day, measured in USD. To achive the result we need to join the onchain XRPL data with prices.
1 2 3 4 5 6 7 8 9 10
WITH ('r3kmLJN5D28dHuH8vZNUZpMC43pEHpaocV/USD', 'rvYAfWj5gh67oV6fW32ZzP3Aw4Eubs59B/BTC') AS issuer_pair SELECT toDate(dt) AS dt, SUM(xrpAmount * value) AS volume_usd FROM xrp_dex_volume INNER JOIN daily_metrics_v2 USING (dt) WHERE (metric_id = dictGet('metrics_by_name', 'metric_id', 'daily_avg_price_usd')) AND (asset_id = dictGet('assets_by_name', 'asset_id', 'xrp')) AND ((takerPaysIssuerCurrency IN (issuer_pair)) OR (takerGetsIssuerCurrency IN (issuer_pair))) GROUP BY dt ORDER BY dt DESC LIMIT 100
Test in Queries