Crypto analysis tools and tips

Master analytics with Flipside's expert tips delivered to your inbox

🧠 Trading data: comparing volumes across DEXs

Today, we'll show you how to find DEX transaction volumes and other values to analyze macro crypto market trends.

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_dex_swaps.

As always, let's start with a basic query to get a feel for the table and what it covers:

Select * from crosschain.core.ez_dex_swaps
Limit 10;

2. Refine your SQL query

Now, let's work on query optimization and filtering for only the information we actually want:

• Replace '*' with the columns we need
  • block_timestamp :: date as day  to create a reference time
  • count (distinct trader) as unique_daily_traders to measure traders
  • sum(unique_daily_traders) over (order by day asc) as cumulative_traders so we can get a sum total of daily active traders in ascending order
• Use the 'where' clause to direct the search
  
  • where block_timestamp >= '2023-4-1' sets our time frame
    
  • and blockchain = 'avalanche'
  • and platform = 'trader_joe_v2'
  • and token_in = '(0x...)'
    
  • and token_out = '(0x...)'

We can set the details of the where clause to focus on any DEX, and any tokens. Think of token_in as tokens traded, and token_out as tokens received, and you can customize this query however you'd like!

In the end, our query should look like this:

Select
block_timestamp :: date as day,
count (distinct trader) as unique_daily_traders,
sum(unique_daily_traders) over (order by day asc) as cumulative_traders
from crosschain.core.ez_dex_swaps

where block_timestamp >= '2023-4-1'

and blockchain = 'avalanche'
and platform = 'trader_joe_v2'

and token_in = '(0x...)'

and token_out = '(0x...)'

 

group by day

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.

In this query, we have multiple ways we can display this data using the X and Y axes.

Set the X axis to Day and set two Y axis values - unique_daily_traders as a bar value and cumulative_traders as a line value. This way, you'll see cumulative growth overlaid with daily activity. Pretty neat!

(We can also calculate additional values using our chosen token_in and token_out values, such as the sum of tokens traded and the difference between them. To do this, add the following to our select function:

sum(amount_out) as weth_bought_daily,

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:

- You can set more than one value per axis on your charts! Play around with visualizing the same data in different ways, or comparing different data in the same chart

- You don't have to type the name of the table you want to query! Shortcut: tap the » that appears when you hover over a table in the 'explore data' dropdown to automatically add it to your query editor!