Crypto analysis tools and tips

Master analytics with Flipside's expert tips in your inbox

🧠 Crosschain data: how to compare token prices

Today, we're learning how to create a price chart for free that compares two or more tokens from different blockchains, one of many things you can do with crosschain data on Flipside

Watch this video for a detailed tutorial, or scroll down to read a quick overview👇

✏️ Tutorial overview: 

1. How to query crosschain tables

In the 'Explore Data' tab in your query engine, select crosschain > core > ez_hourly_prices.

Start with a basic query to get a feel for the table and what it covers:

Select * from crosschain.core.ez_hourly_prices
Limit 10;

2. Refine your SQL query

To get more useful data, we need to format queries to filter for only the data we specifically want:

• Replace '*' with the columns we need
• Use the 'where' clause to direct the search
  
  • where hour >= current_date() - 30 will give us a one-month time frame
  • where token_address IN ( lower('0x...), ('0x...)) focuses on the tokens we want (replace ('0x...') with your token addresses, separated by commas)

In the end, our query should look like this:

select hour, symbol, price from crosschain.core.ez_hourly_prices

where hour >= current_date() - 30

and token_address IN ( lower('0x........'), ('0x........'))

3. Chart data

With the above query, you'll get a results displayed in a table. To create a chart that's much easier on the eyes, click "add chart" at the bottom left of your screen.

Set the X axis to hour, Y axis to price, and the group-by value to symbol. Your results will now be displayed as a shareable price chart!

4. Tips and query optimization

SQL is full of tips and tricks to optimize queries or speed up your SQL workflow. Here are a few from the video:

- Keep Etherscan bookmarked to quickly look up token addresses

- Highlight your code and hit cmd+/ for mac, ctrl+/ for windows to comment it out.

- Use the like operator to filter for similar results and append text with '/%' to signify additional characters to be filled.

(Example: where token_address like 'ibc/%')