Ready, Set, GO!!!!
$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 aUNION ALL
function, and we are not using aWITH
function as we learnt in Chapter 4.
Quick learn: TheUNION 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 aboutUNION
andUNION 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 aholder_balance
for each uniqueholder_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.
Really great and detailed explanation, thank you!