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