Chapter 8 - Big Boy Dashboard - Part III
Squeezing data out of the figurative lemon.
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.
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:
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:
This pool is made up of $WRAB and $WETH.
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
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:
poolto the data columns as aggregator transactions were being shuffled up as they used the same tx hash for multiple swaps.
LEFT JOINis now made on both the
poolthis 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 NULLremoves all the liquidity pool injections.
Results of the patch:
111 swaps, all checking out to be purrfeeect!
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!!!