Chapter 7 - Big Boy Dashboard - Part II

This is the part where you crack your knuckles.

May 30, 2022

$WRAB holder queries

Calculating $WRAB transactions

SELECT
    `to` AS holder_address,
    value AS amount_transfer
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = '0x45d74446748fb432f05e7a85bd974abb7af5c285'
    
UNION ALL
    
SELECT
    `from`AS holder_address,
    (-1 * value) AS amount_transfer
FROM erc20_ethereum.evt_Transfer
WHERE contract_address = '0x45d74446748fb432f05e7a85bd974abb7af5c285'

The above piece of code will be the core component of our greater $WRAB query, let’s have a look at what’s inside:

You will notice that there are 2 SELECT FROM segments which are held together with a UNION ALL function, and we are not using a WITH function as we learnt in Chapter 4.
Quick learn: The UNION ALL function merges table vertically, by adding all the rows of data into one big table. (Note: it only works if all tables merged have identical columns). Click here to learn more about UNION and UNION ALL.
We are using the Dune V2 erc20_ethereum.evt_Transfer table which is an abstraction that let’s you browse all the transfers of ERC-20 tokens on Ethereum.
We learnt a bit about abstractions in Chapter 4.
```
'0x45d74446748fb432f05e7a85bd974abb7af5c285'
```
And this is the contract address of the $WRAB token.
In the first segment of the code we are using ‘to’ and ‘value’ while in the second segment we are using ‘from’ and (-1 * value).
The reason for this particular code is:
- Every address who received $WRAB is listed in the ‘to’ column and can be attributed a positive value on the value processed in the transaction.
- While every address that sent $WRAB is listed in the ‘from’ column and can be attributed a negative value on the value processed in the transaction.

Now, let’s hit the ‘Run’ button to test it out. (Because Shibuya is a relatively new project, it has very little transactions, which makes it ideal to build for)

Calculating $WRAB balances

What we got all the $WRAB transactions in a single table, let’s clean that data up to know the balance of each $WRAB holder.

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

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

What I did:

I wrapped the previous query into a WITH function; and
I grouped all the transactions with similar holder_address to get a holder_balance for each unique holder_address.

Results here:

Now stop patting yourself in the back, we are not out of the woods yet!

Double-checking yourself

“I am the wisest man alive, for I know one thing, and that is that I know nothing.” Socrates

This part can equally be most annoying or the most exhilarating part of data analytics, for it requires introspection.

As at now, we are assuming that our query is right, and our results perfect because our logic worked fine earlier, but let’s double check our work.

We know from Etherscan that the total amount of $WRAB existing is 69,420,420, so let’s double check it out by amending our previous query to calculate the sum of all balances across all addresses holding $WRAB:

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

SELECT
    SUM(amount_transfer) AS total_token
FROM wrab_transaction

Results here:

There is something wrong!!!

41,041,264,640 not equal to 69,420,420

It should technically not be possible to have addresses with negative balances, you can’t send out more than what you receive, let’s sort the previous data by ascending order.
Oh boy!!!

Finding the source of the problems

Hitting such roadblocks is what makes data analytics a challenging discipline.

So let’s test the mettle of both our logic and knowledge:

What is the holder_address 0x0000000000000000000000000000000000000000?

What is that address?
- This is a null address, developers use the null address as the token creator address, which is why it shows a big net negative balance that looks strangely like the total number of token created, with the wrong number of decimal places.
How to solve that problem?
- We kick it out of the query results.

AND `from` != '0x0000000000000000000000000000000000000000'

Why is balance -6.942042e+25?

What is e+25?
- Because we tend to deal with a lot of zeroes, the exponential term is often used, e+25 means 10^25, this helps code readability because else you would need to stick your finger on the screen to count all the zeroes.
Why is there 25 zeroes?
- A $WRAB can be split up to an 18 decimal place amount, and when we count in blockchain speak, we always use the smallest unit, which is why all the results we obtained are 10^18 if we look a unit $WRAB.
How to solve that problem?
- How to solve that problem?
  We divide the values we get in our code by 10^18.
  Quick learn: 1e18 is used as a numeric representation of 10^18, this will come in handy a lot of times when dealing with decimals.

value/1e18 AS amount_transfer

Can we fix it? YES WE CAN!

Now let’s implement some patches and see how close we get to perfecting our results

Updated code:

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'
    )

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

Updated results:

Double checking for total token balance:

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'
    )

SELECT
    SUM(amount_transfer) AS total_token
FROM wrab_transaction

Nice!!! we got a perfectly round 69,420,420

Double checking for negative token balance:

Hmm, some small negative balances are still there, most likely some rounding errors, let’s pretend we did not see anything here.

Takeout

That’s a whole lot of brainwork for barely 20 lines of code. So next time someone asks how much you charge per line of code, you know what to do.
But most important we broke into the whole thought process of surfacing data, questioning it and refining it, it is a big personal breakthrough.

Now let’s get that data presentable!

Journey to the Centre of Arakis