Chapter 11 - Big Boy Dashboard - Part VI

The finishing touches.

1chioku

Jul 05, 2022

In Chapter 10, we learned a bit on ERC-1155 and on how to handle them.
We also had our first interactions with partition windows.

Now let’s get some more analytics done on $WRPP.

More $WPPP queries

$WRPP Staking and Unstaking

The Second question that we want to be answered is on the staking of $WRPP:

How many passes have been staked?
How many passes have been unstaked?

To do that we can run a simple query by analysing the traffic to and from the staking contract located here.

SELECT 
    evt_block_time AS time,
    value AS value
FROM erc1155_ethereum.evt_TransferSingle
WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
AND `to` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
AND id = 1
        
UNION ALL
        
SELECT 
    evt_block_time AS time,
    (-1 * value) AS value
FROM erc1155_ethereum.evt_TransferSingle
WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
AND `from` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
AND id = 1

The data is squeaky clean, but a bit to granular for presentation, let’s:

Truncate the time data to the nearest hour and sum up all the values in those specific hours.

Quick learn: date_trunc(field,expr) will be one of your bread and butter functions when grouping noisy data, be sure to master that properly. Always remember: whatever is truncated, needs to be grouped!

SELECT
    a.hour AS hour,
    SUM(a.value) AS value
FROM
    (
    SELECT 
        date_trunc('HOUR', evt_block_time) AS hour,
        SUM(value) AS value
    FROM erc1155_ethereum.evt_TransferSingle
    WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
    AND `to` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
    AND id = 1
    GROUP BY hour
    
    UNION ALL
    
    SELECT 
        date_trunc('HOUR', evt_block_time) AS hour,
        (-1 * SUM(value)) AS value
    FROM erc1155_ethereum.evt_TransferSingle
    WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
    AND `from` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
    AND id = 1
    GROUP BY hour
    ) a
GROUP BY hour

Not bad, but I think we can present in an even better way with our newly acquired partition windows skill!

SELECT
    b.hour,
    SUM(b.value) OVER (ORDER BY b.hour ASC) AS cumulative_passes_staked_unstaked
FROM
    (
    SELECT
        a.hour AS hour,
        SUM(a.value) AS value
    FROM
        (
        SELECT 
            date_trunc('HOUR', evt_block_time) AS hour,
            SUM(value) AS value
        FROM erc1155_ethereum.evt_TransferSingle
        WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
        AND `to` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
        AND id = 1
        GROUP BY hour
        
        UNION ALL
        
        SELECT 
            date_trunc('HOUR', evt_block_time) AS hour,
            (-1 * SUM(value)) AS value
        FROM erc1155_ethereum.evt_TransferSingle
        WHERE contract_address = '0x97ed92e744c10fdd5d403a756239c4069e415e79'
        AND `from` = '0xfa0b8feca230818439afe23a0da24579338cbda3'
        AND id = 1
        GROUP BY hour
        ) a
    GROUP BY hour
    ) b

And voila! We can fully observe the passes being staked and unstaked through time.

$WRPP Voting

As we mentioned that previously, when you stake your $WRPP you are entitled to a vote, and the people who vote for the winner get bonus $WRAB. Is there as way to get an x-ray vision into how people are voting?

Quick learn: We are now going to touch the ethereum.logs raw data set, which contains information on smart contract data, basically what messages are being passed into a smart contract interaction such as voting.

Let’s start with the final code and reverse engineer the thought process:

SELECT
    c.hour,
    CASE
    WHEN c.vote_choice= 1 THEN 'Left Door'
    WHEN c.vote_choice = 2 THEN 'Right Door'
    END vote_choice,
    SUM(c.vote_weight) OVER (ORDER BY c.hour ASC) AS cumulative_votes
FROM
    (
    SELECT
        b.hour,
        b.data_2 AS vote_choice,
        SUM(b.data_3) AS vote_weight
    FROM
        (
        SELECT
            date_trunc('HOUR', a.block_time) AS hour,
            bytea2numeric_v2(substring(a.data, 3, 64)) AS data_1, -- chapter
            bytea2numeric_v2(substring(a.data, 67, 64)) AS data_2, -- vote choice
            bytea2numeric_v2(substring(a.data, 131, 64)) AS data_3 -- vote weight
        FROM
            (
            SELECT
                block_time,
                data
            FROM ethereum.logs
            WHERE topic1 = '0x329dc4322e1eca93c98ac9c5f53e4213d68c0fcbcb08895b3b80a886a0051cee'
            ) a
        ) b
    WHERE b.data_1 = 1
    GROUP BY b.hour, b.data_2
    ) c

By now your eyes should be able to read code a bit better, but anyhow let’s have a brief overlook at the code structure.

SELECT
    block_time,
    data
FROM ethereum.logs
WHERE topic1 = '0x329dc4322e1eca93c98ac9c5f53e4213d68c0fcbcb08895b3b80a886a0051cee'

At very bottom is a short query that filters the ethereum.logs for entries where topic1 = '0x329dc4322e1eca93c98ac9c5f53e4213d68c0fcbcb08895b3b80a886a0051cee'.
Topics are essentially unique ids for smart contract calls, think of it as a unique id, and this case this is the unique id for voting using $WRPP.
Below is a sample log.

Next we truncate the time and decode the data:

SELECT
    date_trunc('HOUR', a.block_time) AS time,
    bytea2numeric_v2(substring(a.data, 3, 64)) AS data_1, -- chapter
    bytea2numeric_v2(substring(a.data, 67, 64)) AS data_2, -- vote choice
    bytea2numeric_v2(substring(a.data, 131, 64)) AS data_3 -- vote weight
FROM (table a)

At this point in time it is not important to understand how those functions work exactly, but to put it into layman terms:
- substring( data, position_x, length_y) snips the long data into 64 alphanumeric pieces starting from position_1 for length_y.
- bytea2numeric_v2() converts the bytea data into human readable numeric data. In our case it tells us the chapter number, the vote choice and the vote weight(no. of passes staked).

Then we group them as as temporary step:

SELECT
    b.hour,
    b.data_2 AS vote_choice,
    SUM(b.data_3) AS vote_weight
FROM (table b)
WHERE b.data_1 = 1
GROUP BY b.hour, b.data_2

Last but not least, we add the final wrapper:

SELECT
    c.hour,
    CASE
    WHEN c.vote_choice= 1 THEN 'Left Door'
    WHEN c.vote_choice = 2 THEN 'Right Door'
    END vote_choice,
    SUM(c.vote_weight) OVER (PARTITION BY c.vote_choice ORDER BY c.hour ASC) AS cummulative_votes
FROM (table c)

In the final wrapper, we tag the choices using the CASE statement.
And we use some partition windows to get the cumulative data. Note that we added the PARTITION BY function in our partition window, this is because we want the cumulative function to be conducted separately for each vote_choice.

Results:

Cloning the query for Chapter 2:

SELECT
    c.hour,
    CASE
    WHEN c.vote_choice= 1 = 1 THEN 'Fox'
    WHEN c.vote_choice= 2 THEN 'Air Drop'
    WHEN c.vote_choice= 3 THEN 'Lady'
    END vote_choice,
    SUM(c.vote_weight) OVER (PARTITION BY c.vote_choice ORDER BY c.hour ASC) AS cumulative_votes
FROM
    (
    SELECT
        b.hour,
        b.data_2 AS vote_choice,
        SUM(b.data_3) AS vote_weight
    FROM
        (
        SELECT
            date_trunc('HOUR', a.block_time) AS hour,
            bytea2numeric_v2(substring(a.data, 3, 64)) AS data_1, -- chapter
            bytea2numeric_v2(substring(a.data, 67, 64)) AS data_2, -- vote choice
            bytea2numeric_v2(substring(a.data, 131, 64)) AS data_3 -- vote weight
        FROM
            (
            SELECT
                block_time,
                data
            FROM ethereum.logs
            WHERE topic1 = '0x329dc4322e1eca93c98ac9c5f53e4213d68c0fcbcb08895b3b80a886a0051cee'
            ) a
        ) b
    WHERE b.data_1 = 2
    GROUP BY b.hour, b.data_2
    ) c

As you can see the biggest difference for Chapter 2 is the options we use for the CASE statement in the final wrapper and where we set WHERE b.data_1 = 2.

Results:

P.S: I initially wrote the query in DatatabricksSQL for V2, but I had some issues with the run speed so I rewrote them in PostgreSQL on V1. I added all of the queries below so you can compare.

Chapter 1 Vote - V1 Link, V2 Link

Final result

It’s been a long ride, but we finally finished our Big Boy Dashboard!

Summary

In the Big Boy Dashboard we touched almost everything: from ERC-20 tokens to ERC-1155 tokens; from abstractions to raw data; and introduced a whole array of toolset to help up process and visualise data.
With the completion of this dashboard, you can finally call yourself an apprentice wizard.

Looking forward to the next chapter!

Journey to the Centre of Arakis

Discussion about this post