Chapter 8 - Big Boy Dashboard - Part III

Squeezing data out of the figurative lemon.

May 30, 2022

In Chapter 7, we ended up with a 20 line query that got us all the latest balance of every Ethereum address that transacted with the $WRAB ERC-20 token.
We double checked the query results and they tally, which means our data is good to move forward with.

Extrapolating useable data

“A master with a stone beats the apprentice with a shuriken” I think I read that in Naruto or something.

The 2 columns we got from Chapter 7 seem quite the slim pickings at first sight, but with a bit of imagination we can get turn that into useable data, so let’s get to it.

Ideas

Let’s sort the rows by descending order, largest to smallest;
Let’s remove the zero balance users that sold off all their tokens;
Let’s add a column that shows the % that the holder owns of the total tokens that exist; and
Let’s add external Etherscan URL for viewers to interact with our dashboard.

WITH wrab_transaction AS
    (
    SELECT
       `to` AS holder_address,
        value/1e18 AS amount_transfer
    FROM erc20_ethereum.evt_Transfer
    WHERE contract_address = '0x45d74446748fb432f05e7a85bd974abb7af5c285'
    
    UNION ALL
    
    SELECT
        `from`AS holder_address,
        (-1 * value)/1e18 AS amount_transfer
    FROM erc20_ethereum.evt_Transfer
    WHERE contract_address = '0x45d74446748fb432f05e7a85bd974abb7af5c285'
    and `from` != '0x0000000000000000000000000000000000000000'
    ) ,

wrab_holder AS
    (
    SELECT
        holder_address,
        SUM(amount_transfer) AS holder_balance
    FROM wrab_transaction
    GROUP BY holder_address
    )

SELECT
    holder_address,
    '<a href="https://etherscan.io/address/' || substring(holder_address from 1) ||'" target="_blank" >' || "Link to Etherscan" || '</a>' as etherscan_url,
    holder_balance,
    (holder_balance / 69420420) * 100 AS perc_total_supply
FROM wrab_holder
WHERE holder_balance > 0
ORDER BY holder_balance DESC

Now, let’s create a separate table which we will label neatly:

And voila:

What else can we squeeze out?

Pie chart of holders:

% $WRAB in Treasury:

So the big holder that controls 93.65% of all $WRAB is the Shibuya treasury, it’s basically the locked/not yet released $WRAB.

Seems good enough for now, let’s drop that in our dashboard.

Shibuya Dashboard v0.1

Not bad ey!

$WRAB DEX queries

Preliminary ground work

Normally there is an abstraction that give you all the DEX trades on Ethereum, and thus gives you the accurate pricing for low liquidity tokens like $WRAB.

On Dune V1 this is called dex."trades", however, given that as the moment of writing this substack, the DEX trades abstraction is not available, we will have to figure out how to get the prices of $WRAB the old fashion way.

First let’s use Dune V1 to peak into where $WRAB DEX trades are occurring.

SELECT *
FROM dex."trades"
WHERE "token_a_address" = '\x45d74446748fb432f05e7a85bd974abb7af5c285'
OR "token_b_address" = '\x45d74446748fb432f05e7a85bd974abb7af5c285'

Cool, so if you look at the category section, we have aggregators and DEXes:

Aggregators are like the brokers of the DeFi world, they just route the trades to the DEXes, so we don’t really care for them because the trades don’t happen at their level.
DEXes on the otherside are where the trades are executed , so scrolling through that, we can see that there are trades on Uniswap and Sushiswap.
And these trades are occurring on the following exchange contract addresses:
- Sushiswap: ‘0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb’
  - This pool is made up of $WRAB and $WETH.
- Uniswap: ‘0x3429e990337542434f6bfac5a4d9f14ed285ac7a’
  - This pool is made up of $WETH and $WRAB.

Bingo! These exchange contracts are all that we need to narrow down the $WRAB DEX activities.

$WRAB DEX activities

To get the $WRAB DEX activities, let’s scrape something from erc20_ethereum.evt_Transfer.

WITH transaction_in AS
    (
    SELECT
        evt_block_time,
        evt_tx_hash,
        contract_address,
        value
    FROM erc20_ethereum.evt_Transfer
    WHERE `to` = '0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb'
    OR `to` = '0x3429e990337542434f6bfac5a4d9f14ed285ac7a'
    ),
    
    transaction_out AS
    (
    SELECT
        evt_block_time,
        evt_tx_hash,
        contract_address,
        value
    FROM erc20_ethereum.evt_Transfer
    WHERE `from` = '0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb'
    OR `from` = '0x3429e990337542434f6bfac5a4d9f14ed285ac7a'
    )

SELECT
    transaction_in.evt_block_time,
    transaction_in.evt_tx_hash,
    transaction_in.contract_address AS token_a,
    transaction_in.value AS token_a_value,
    transaction_out.contract_address AS token_b,
    transaction_out.value AS token_b_value
FROM transaction_in
LEFT JOIN transaction_out
ON transaction_in.evt_tx_hash = transaction_out.evt_tx_hash

By now you should be able to read the above quite fluently. If you have a bit of trouble, here is the primer:

All swap transactions involve injecting a token into a pool and removing the opposing pair out of the pool, this is basic AMM.
By pairing ERC-20 transfer transactions with the pool addresses that we obtained, and joining them at the transaction hash level which is unique, we get the equivalent of a swap table.

So we have 146 transactions that interacted with the Uniswap and the Sushiswap pools, but we still have some debugging to do, there seems to be some anomalies in our query results.

Taking a closer look, we will find 2 anomalies:

Transaction 0xc40ca4c9e40a7d6e8c49b68165c442d94b3ec28609ba2627a7ded08e78b47dd6 is a transaction with null value in the token section.
If you open up the transaction, you will see that this is not a swap transaction, but a liquidity provisioning transaction. We need filter those out of out results.

Transaction 0xb7ede57b19db077125fe0393b269a6b0379716091d9cd7da56f5beb6fa6358e9 is repeated twice.
If you open up the transaction, you will see that this is an aggregator transaction. We need to rearrange them those so we don’t have duplicate transations in our table.

Let’s patch this up

After a few trials and errors I’ve come up with the following:

WITH transaction_in AS
    (
    SELECT
        evt_block_time,
        evt_tx_hash,
        contract_address,
        `to` AS pool,
        value AS value
    FROM erc20_ethereum.evt_Transfer
    WHERE `to` = '0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb'
    OR `to` = '0x3429e990337542434f6bfac5a4d9f14ed285ac7a'
    ) ,
    
    transaction_out AS
    (
    SELECT
        evt_block_time,
        evt_tx_hash,
        contract_address,
        `from` AS pool,
        value AS value
    FROM erc20_ethereum.evt_Transfer
    WHERE `from` = '0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb'
    OR `from` = '0x3429e990337542434f6bfac5a4d9f14ed285ac7a'
    ) ,

all_dex_transaction AS
    (
    SELECT
        transaction_in.evt_block_time,
        transaction_in.evt_tx_hash,
        transaction_in.pool,
        transaction_in.contract_address AS token_a,
        transaction_in.value AS token_a_value,
        transaction_out.contract_address AS token_b,
        transaction_out.value AS token_b_value
    FROM transaction_in
    LEFT JOIN transaction_out
    ON transaction_in.evt_tx_hash = transaction_out.evt_tx_hash
    AND transaction_in.pool = transaction_out.pool
    )

SELECT *
FROM all_dex_transaction
WHERE token_b IS NOT NULL

Comments on the patch:

We added pool to the data columns as aggregator transactions were being shuffled up as they used the same tx hash for multiple swaps.
The LEFT JOIN is now made on both the evt_tx_hash and the pool this guarantees that even though the tx hash is the same, we ensure that we separate transactions that go to different pools.
A final filter WHERE token_b IS NOT NULL removes all the liquidity pool injections.

Results of the patch:

111 swaps, all checking out to be purrfeeect!

Takeout

We are running out of space again in the substack, to keep the thoroughness consistent we have to keep dashboard walkthrough meticulously detailed.
Anyhow, we managed to get the first segment complete and are engineering complete DEX transactions from simple abstractions, we are on FIRE!!!

Let’s keep pushing through, we are halfway there!!!

Journey to the Centre of Arakis

Discussion about this post