Description of work

In this Bounty we want to know where the addresses of the thorchain ecosystem wallets get their first RUNE asset when they are created and If it's a swap, which chain / asset are they coming from? To do this, we consider several types:

• Receive RUNE from a direct transfer
• Receive RUNE from a centralized exchange
  • Binance CEX addresses: thor1t60f02r8jvzjrhtnjgfj4ne6rs5wjnejwmj7fh (Binance Cold Wallet)
  • Binance CEX addresses: thor1uz4fpyd5f5d6p9pzk8lxyj4qxnwq6f9utg0e7k (Binance Hot Wallet)
  • Crypto.com CEX address: thor1ty6h2ll07fqfzumphp6kq3hm4ps28xlm2l6kd6)
• Receive RUNE from a swap
• Receive RUNE upgrade from BNB.RUNE or ETH.RUNE

Note

• We use the [thorchain] schema, [transfers], [swaps] and [upgrades] tables
• To find the addresses that received their first RUNE from the centralized exchange we have in the table [transfers], for each address we select the first record (min(block_timestamp) for each to_address) that received from the centralized exchange
• To find the addresses that received their first RUNE from the direct transfer we have in the table [transfers], for each address we select the first record (min(block_timestamp) for each to_address) that did not receive from the centralized exchange
• To find the addresses that received their first RUNE from the swap we have in the table [swap], for each address, we select the first record (min(block_timestamp) for each from_address) that swap from an asset to RUNE
• To find the addresses that received their first RUNE from the upgrade from BNB.RUNE or ETH.RUNE we have in the table [upgrades], for each address, we select the first record (min(block_timestamp) for each from_address) that upgrade from BNB.RUNE or ETH.RUNE to RUNE
• Finally, we get all the addresses and min(block_timestamp) in each section and union them, and among them we consider all the addresses with the final min (block_timestamp) from each type (centralized exchange, swap, upgrade, direct transfer) as output.
  • row_number() over(partition by wallet_address order by first_block_timestamp) as row_num
• To find chain / asset in swap, we first find the list of addresses whose first RUNE was received from swap according to the previous description, then we join these addresses with [swaps] table on first_block_timestamp and from_address , and for these records we find chain and asset.

1 ---> First getting $RUNE by new wallet

According to the results, it is clear that

• The total number of unique addresses we counted from the transfer, swap and upgrade table is 109409
• Of these 109409 wallet addresses
  • 46744, they have received their first RUNE from direct transfer, something about 42.7%
  • 45780, they have received their first RUNE from swap, something about 41.8%
  • 16163, they have received their first RUNE upgrade from BNB.RUNE or ETH.RUNE, something about 14.8%
  • 722, they have received their first RUNE from centralized exchange, something about 0.66%
• It can be seen that the most wallet creation is related to Apr 8, 2022, Sep 27, 2021 and Nov 3,2021, whose first received are more than direct transfer and swap over time.

2 ---> In swap, which chain / asset are they coming from?

According to the results, it is clear that

• In Swap wallet addresses receive their first RUNE more than BNB, ETH, BTC and TERRA.UST assets

According to the results, it is clear that

• In Swap wallet addresses receive their first RUNE more than BNB, ETH, BTC, TERRA and LTC blockchains

Conclusion

According to the results of the first RUNE of each wallet in the thorchain ecosystem, it has been determined that the new wallet addresses receive their first RUNE mostly through direct transfer and then swap, as well as users who receive their first RUNE from swap, They have been more receive RUNE from BNB, ETH, BTC and TERRA.UST asset and more on BNB, ETH, BTC, TERRA and LTC blockchains.