An exploration in the XRP ledger with BigQuery
When you get access to the full transaction history on the XRP ledger, exploration opportunities are endless. This article highlights some of the possibilities I have found with my limited imagination.
All the examples are using only transaction data from Google BigQuery provided by Wietse Wind, so nothing such as current wallet balances, and only consider XRP and not any other currencies.
The dataset contains the full available history (32.570 and forward).
1. Wallet payment traffic versus balance
Wallets send XRP, holds XRP and receives XRP. Looking at the sum of all payments sent, and the sum of all payments received we can establish wallet payment activity. We can establish some new variables for wallets:
- Total sent: Sum of all payments sent from a wallet
- Total received: Sum of all payments received from a wallet
- Total traffic: Sum of total sent and the total received
- Total balance: Total received minus total sent
- Max sent: The largest payment sent from a wallet
- Max received: The largest payment received by a wallet
These variables we can use to make assumptions of wallet use, e.g.:
- If wallet has high traffic but low balance, the wallet is not used for holding XRP but used as e.g. a hot wallet for an exchange or interim wallet for OTC transfers or similar.
- If wallet has received many funds, but not sent many funds, it is likely used as a cold wallet for holding XRP.
- If wallet has sent funds but never received any, it is likely an inactive genesis wallet (received funds before ledger 32.570, never since).
- Using the max sent variable, we can find wallets that have sent many payments but kept the payments in a size that would go unnoticed by XRPL Monitor.
Since I am not looking for anything specific in these queries, it, however, reveals some interesting things. E.g. a “stress test” wallet that has been used to transfer 345,6B XRP in transactions not larger than 1.6M. Or this wallet, that has received 546,7M XRP, but also sent the same amount, but never in larger payments than 4,35M.
2. Network amongst most sending wallets
Making a graph of the 50.000 wallets that has sent the most XRP all time, a network can be visualised.
Parameters:
- Top 50.000 most sending wallets
- All payments amongst these wallets, not including total payments of less than 500 XRP. Any payments out of the network are not included.
- Clustered using a modularity algorithm to reveal communities or sub-networks, using this for colours
- Nodes are sized based on centrality and lines represent payments
Adding data to the graph shows some interesting connections — especially some of the nodes connecting to the isolated communities, that are unnamed wallets. (I am sorry about the overlapping labels).
3. Network around a wallet
In this example, I am using one wallet as a starting point (why not the XRP Tip Bot?) and their internal payments.
The XRP Tip Bot wallet has sent payments to 685 other wallets, but connections amongst these wallets amount to a total of 2.839 lines.
Scaling it out to the next degree of connections results in almost 500.000 wallets!
As one might assume, the clusters are primarily based around exchanges. The same graph below with labels.
Final remarks
There are almost endless possibilities with the full history available in an accessible form like this. I would love to dig further into the dataset and bring out interesting facts — please leave your ideas and remarks in the comments.
If you like what you’ve read or seen, tips are appreciated.
Cross post from the XRP Community Blog.
