Chapter 5 - JOIN the party

Sticking two lego blocks together.

May 25, 2022

One thing we have not touched yet is aliases. Sometimes developers call name data columns as they see fit, and we the analysts are faced with the necessity to rename them.

Often you will see the function AS next to a queried data. Using the query from Chapter 4, we will try to use aliases to make our query more human readable.

Initial query:

WITH temp AS
    (
    SELECT
        minute,
        price,
        symbol,
        contract_address
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'UNI'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    ORDER BY minute DESC
    )
    
SELECT
AVG(price)
FROM temp

Now with aliases:

WITH temp AS
    (
    SELECT
        minute AS time,
        price AS token_price,
        symbol AS token_symbol,
        contract_address AS token_address
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'UNI'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    ORDER BY minute DESC
    )
    
SELECT
AVG(token_price) AS average_price
FROM temp

The end result is the same, but in terms of readability, we can comprehend in an easier way that in the last 3 lines, we are looking for the average of the column that has all the token_prices and that this result will be named average_price.

As your queries get longer and more complex, proper aliasing will save you from major headaches.

Merging data from different tables

Data tables are often specialised because it is practically impossible to work with one big master data table that contains everything, the machines would literally catch fire.

This means that often we have to pull data from many data tables based on our needs and smash them into an even more specialised one that we can use.

If you feel technical enough, you can go have a read on primary and foreign keys.

Else we will try to pick up on what those means in this chapter using our simpler terms and practical examples.

UNI in WETH

In Chapter 4, we learned about breaking down our thought processes and our query. Let’s reuse the same process to learn about JOIN and try to get out query to give us the price of UNI in WETH.

I want all the UNI token prices between '2022-04-19 23:59' and '2022-04-26 23:59';
I want all the WETH token prices between '2022-04-19 23:59' and '2022-04-26 23:59';
I want to see the price of WETH and UNI in the same table next to each other so that I can perform the arithmetic calculation:
price_of_uni/price_of_weth
I want to see the price of UNI in WETH for each minute in descending order.
I want to plot that in a simple line chart.

Step 1 - Price of UNI

Easy peasy.

SELECT
    minute AS time,
    price, AS price_of_uni
FROM prices.usd
WHERE blockchain = 'ethereum'
AND symbol = 'UNI'
AND minute > '2022-04-19 23:59'
AND minute < '2022-04-26 23:59'

Step 2 - Price of WETH

Lemon squeezy.

SELECT
    minute AS time,
    price, AS price_of_weth
FROM prices.usd
WHERE blockchain = 'ethereum'
AND symbol = 'WETH'
AND minute > '2022-04-19 23:59'
AND minute < '2022-04-26 23:59

Step 3 - JOIN into a big table

Now to merge all of the results together, we use the JOIN function.

There are many types of joins, we will start by introducing the easiest one, LEFT JOIN. If you want to go read more on JOIN here is short recap.

LEFT JOIN merges select data columns from 2 or more tables based on a common data point.

In our case below, we are merging the uni_price table and the weth_price table using uni_price.minute and weth_price.minute as common data point.

A LEFT JOIN in specific, uses the first table as main anchorage table and adds the values for which the common data point exists.

WITH uni_price AS
    (
    SELECT
        minute AS minute,
        price AS price_of_uni
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'UNI'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    ) ,
    
weth_price AS
    (
    SELECT
        minute AS minute,
        price AS price_of_weth
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'WETH'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    )

SELECT
    uni_price.minute AS minute,
    uni_price.price_of_uni AS price_of_uni,
    weth_price.price_of_weth AS price_of_weth
FROM uni_price
LEFT JOIN weth_price
ON uni_price.minute = weth_price.minute

And the results are:

Step 4 - UNI in WETH

Now to get our finalised data, which is UNI in WETH:

WITH uni_price AS
    (
    SELECT
        minute AS minute,
        price AS price_of_uni
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'UNI'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    ) ,
    
weth_price AS
    (
    SELECT
        minute AS minute,
        price AS price_of_weth
    FROM prices.usd
    WHERE blockchain = 'ethereum'
    AND symbol = 'WETH'
    AND minute > '2022-04-19 23:59'
    AND minute < '2022-04-26 23:59'
    ) ,

data_table AS
    (
    SELECT
        uni_price.minute AS minute,
        uni_price.price_of_uni AS price_of_uni,
        weth_price.price_of_weth AS price_of_weth
    FROM uni_price
    LEFT JOIN weth_price
    ON uni_price.minute = weth_price.minute
    )

SELECT
    minute,
    (price_of_uni/price_of_weth) AS price
FROM data_table

And the results are:

Step 5 - Final Chart

And now to put the final touch, we have a chart of UNI/WETH.

Takeouts

Often data needs to be pulled from different sources, or processed through different queries in order to achieve a final result.
To merge data from different tables we can use the JOIN function.

Are you ready to take off the training wheels?

Journey to the Centre of Arakis

Discussion about this post