Recap
In Chapter 8, we managed to recreate a table that has all the unique DEX based swaps with the $WRAB token.
We have isolated on that table, the timestamp, the unique tx hash, the pool where the swap was done, but most importantly which tokens and which quantity was swapped.
Extrapolating useable data
Same as for the last chapter we will be looking at using our imagination to extrapolate useable data from our initial dataset.
Ideas
Let’s calculate the price of $WRAB in $WETH as we seem to already have both readily available.
Let’s rename the pools as Uniswap and Sushiswap so the viewers do not have to guess which pools those long addresses represent.
Let’s also get a terminal price of the $WRAB and it’s fully diluted market capitalisation in USD.
Code here:
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
) ,
clean_dex_transaction AS
(
SELECT
DATE_TRUNC('minute',evt_block_time) AS minute,
evt_tx_hash,
pool,
token_a,
token_a_value,
token_b,
token_b_value
FROM all_dex_transaction
WHERE token_b IS NOT NULL
) ,
This piece of code above is the same as we previously reached in Chapter 8.
weth_price AS
(
SELECT
minute AS minute,
price AS weth_price
FROM prices.usd
WHERE blockchain = 'ethereum'
AND symbol = 'WETH'
AND minute > '2022-03-01 00:00'
) ,
condolidated_dex_transaction AS
(
SELECT
clean_dex_transaction.minute AS minute,
clean_dex_transaction.pool,
clean_dex_transaction.token_a,
clean_dex_transaction.token_a_value,
clean_dex_transaction.token_b,
clean_dex_transaction.token_b_value,
weth_price.weth_price
FROM clean_dex_transaction
LEFT JOIN weth_price
ON clean_dex_transaction.minute = weth_price.minute
) ,
Here we added the price of WETH from the prices.usd
table and used LEFT JOIN
to add that to the main table.
datatable_reorganisation AS
(
SELECT
minute,
pool,
token_a AS weth,
token_a_value AS weth_value,
token_b AS wrab,
token_b_value AS wrab_value,
weth_price
FROM condolidated_dex_transaction
WHERE token_a = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
UNION ALL
SELECT
minute,
pool,
token_b AS weth,
token_b_value AS weth_value,
token_a AS wrab,
token_a_value AS wrab_value,
weth_price
FROM condolidated_dex_transaction
WHERE token_b = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
)
In order to make the next calculations easier, we sorted all the token values into the same column.
Previously in token_a
, token_a_value
, token_b
, and token_b_value
we could find both WETH and $WRAB.
Later we will learn about subqueries, but for now let’s keep things simple.
SELECT
minute,
CASE
WHEN pool = '0x7baa6923ee2f8231d12c87a90f33f5cb7cb043cb' THEN "Sushiswap"
WHEN pool = '0x3429e990337542434f6bfac5a4d9f14ed285ac7a' THEN "Uniswap"
END pool_name,
((weth_value/1e18)/(wrab_value/1e18)) AS wrab_price_in_weth,
((weth_value/1e18)/(wrab_value/1e18)) * weth_price AS wrab_price_in_usd,
((weth_value/1e18)/(wrab_value/1e18)) * weth_price * 69420420 AS wrab_marketcap_in_usd
FROM datatable_reorganisation
ORDER BY minute DESC
And for the final part we will calculate all the data points that we will need to build up visuals, with a little ORDER BY
at the end to make things neater to read.
Quick learn: The CASE
function is a practical function when labelling data, the syntax is quite easy to read and learn.
What can we get from our latest query
A tables with all the latest DEX trades:
A line chart mapping the DEX prices of Uniswap and Sushiswap:
Some useful counters:
Let’s add those to our dashboard.
Shibuya Dashboard v0.2
Nice!
Although in my original Shibuya dashboard there was a lot analytics, for the purposes of getting something useable out, we will stop here for $WRAB.
We have enough analytics for now, so let’s move on to $WRPP!
Takeout
We have been applying all the techniques we have learnt so far in manipulating some simple data from Dune v2.0 abstractions table.
And we have been able to extract actual useable data points that helped us build the section of our dashboard that covers ERC-20 $WRAB tokens.
I want to learn more about NFTs now!