Description of work

In this Bounties we want to knowing how many users are currently in the thorchain ecosystem. To do this, we examine the following:

• Number of active wallet addresses
• How many are holding $RUNE in their wallet?
• How many are have an open LP position?

Note

• We use the [thorchain] schema, [transfers], [liquidity_actions] and [swaps] table
• Our definition of an active wallet address:
  • An active wallet address that has had at least one transaction in the last three months
  • Transaction definition
    • A transaction can be defined as the token transfer, swap or add/remove liquidity

1 ---> Number of active wallet addresses

• To do this
  • Get distinct from_addresses in the [transfers], [swaps] and [liquidity_actions] for last three months
  • Finally count(distinct address)

According to the results, it is clear that:

• According to our definition and assumption of active wallet address, the total number of active wallets in the thorchain ecosystem is 39846 to wallets.
• It can be seen that the most activity and transaction of active wallets in the last three months is related to swap and token transfer
• The highest number of active wallets in the last three months is in Apr
• The highest number of active wallets in the last three months is related to Apr 8, Jun 15 and Jun 16, respectively

2 ---> How many are holding $RUNE in their wallet?

To do this, we join the active wallets to the [liquidity_actions] table and count the [from_address] that:

• Calculate sum(rune_amount) for active address that lp_action = 'add_liquidity' ---> Staking
• Calculate sum(rune_amount) for active address that lp_action = 'remove_liquidity' ---> Unstaking
• Then we consider users whose Stake - Unstake value is greater than 0 RUNE as Holder RUNE

According to the results, it is clear that:

• According to our definition of active wallet and the addresses that RUNE holds in their wallet, from 39846 to the active address, at least 10802 of them holds RUNE in their wallet, which is about 27.10% of them.

3 ---> How many are have an open LP position?

To do this, we join the active wallets to the [liquidity_actions] table and count the [from_address] that:

• Calculate count(tx_id) for active address that lp_action = 'add_liquidity' ---> Open LP position
• Calculate count(tx_id) for active address that lp_action = 'remove_liquidity' ---> Close LP position
• Then we consider users whose Open LP position - Close LP position value is greater than 0. so these address have open LP position

According to the results, it is clear that:

• According to our definition of active wallet and the addresses that have open LP position in their wallet, from 39846 to the active address, at least 5760 of them has open LP position, which is about 14.45% of them.
• It can be seen that the addresses of active wallets from 2022 onwards do more add_liquidity on a daily basis.
• The highest amount of add_liquidity is related to Apr 8, which on this date we have both the largest number of active wallets and the highest number of wallets that holds RUNE
• The highest amount of remove_liquidity is related to the end of Jun, during which we see the lowest number of active wallets and the lowest number of wallets that holds RUNE.