Welcome aboard! You’ve landed on one of our evergreen education bounties - open for everyone at anytime.
Just A copy from Flipside crypto site
This particular SQL guide focuses on the solana.swaps table. We take you on a step by step journey to look at the volume of SOL swapped to token X (USDC in this guide) and the net token flow of SOL-TokenX. During this guide, we will be focusing on Orca Protocol - a “human-centered” DEX on Solana that is “designed for people, not programs.” You can interact with Orca Protocol here: https://www.orca.so/ or read up it's docs here: https://orca-so.medium.com/ At the end of this guide, we’ve included a challenge for you to apply what you’ve learnt to compare SOL-USDC token flow on Orca and Jupiter. The estimated time to go through this guide is 30 minutes - 1 hour.
Navigating to the correct table Let’s get started and navigate to the solana.swaps table on https://app.flipsidecrypto.com/velocity.
Once there, we can select “preview” to display 10 rows of results for that particular table. The preview will resemble: solana.swaps preview • SWAP_PROGRAM indicates the DEX that was used. • BLOCK_TIMESTAMP indicates the time the transaction occurred. The timezone here is UTC+0 • BLOCK_ID indicates the height of the block • TX_ID is the unique transaction ID for that transaction. We can look up transaction IDs on https://solscan.io/ to verify them • SUCCEEDED informs us if the transaction was successful or not • SWAPPER informs us who initiated the transaction • SWAP_FROM_AMOUNT informs us the amount of token being swapped • SWAP_FROM_MINT informs us the token address of the token being swapped • SWAP_TO_AMOUNT informs us the amount of token being swapped for • SWAP_TO_MINT informs us the token address of the token being swapped for Alright, now that we have an understanding of the table, let’s figure out what is the most popular token that users are swapping their SOL for on Orca. Prior to writing our query, we know that:
Since each row indicates the DEX, we can adjust our query to only consider swaps on Orca
We are only looking at transactions where the token being swapped from is SOL
We only want to look at successful transactions SELECT SWAP_TO_MINT AS swapped_to_token, COUNT(tx_id) AS count_tx_id FROM solana.swapsWHERE swap_program = 'orca' AND swap_from_mint = 'So11111111111111111111111111111111111111112' AND succeeded = 'TRUE' AND block_timestamp >= current_date - 30 GROUP BY swapped_to_token ORDER BY count_tx_id DESC LIMIT 3 Let’s go through what each line in the above query means: • We start with SELECT which is a syntax to select or display columns of your table • SWAP_TO_MINT is the token address of the token received • COUNT(DISTINCT(tx_id)) to determine the total number unique of swaps • FROM solana.swaps tells us the table we are querying from • swap_program = 'orca' to only consider trades on Orca • AND swap_from_mint = 'So11111111111111111111111111111111111111112' to filter out swaps where the swap from token is not SOL • AND succeeded = 'TRUE' to filter out failed transactions • AND block_timestamp >= current_date - 30 tells the query to only consider transactions from the past 30 days • GROUP BY swapped_to_token near the end because whenever we have an aggregate, like the sum function, we need to tell the query which column we are ‘grouping it’ by. • ORDER BY count_tx_id DESC so that the query orders our result from most to least popular in terms of tokens being swapped to • LIMIT 3 returns the top 3 results Now that we understand what the query does let’s run the above query and see our results.
Through utilizing SolScan, we can determine that the most popular token that the three most popular are:
USDC
GST
Wrapped SOL Given that it is not easy to decipher which token address belongs to which token, let’s modify the above query a bit to make use of the solana.labels table. SELECT address_name AS swapped_to_token, COUNT(tx_id) AS count_tx_id FROM solana.swaps a LEFT JOIN solana.labels b ON a.swap_to_mint = b.address WHERE swap_from_mint = 'So11111111111111111111111111111111111111112' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp >= current_date - 30 GROUP BY swapped_to_token ORDER BY count_tx_id DESC LIMIT 3 The changes we made:
address_name AS swapped_to_token
LEFT JOIN solana.labels b ON a.swap_to_mint = b.address Through utilizing a left join, we are able to return all the results from the left table (solana.swaps) and matching records from the right table (solana.labels) From running this slightly revised query, we can see that the swapped_to_token contains the names of the tokens rather than their address.
Challenge 1: How can the above query be modified to determine the three most popular coins that users on Orca are swapping SOL for between February 1, 2022 and February 28, 2022? Hint: use date_trunc('day', block_timestamp) to filter by dates.
Now that we know that USDC is the most popular coin that SOL is being swapped for (in terms of unique transactions) let's determine the daily volume in terms of SOL being swapped for USDC on ORCA. SELECT date_trunc('day', block_timestamp) as date, SUM(swap_from_amount) AS volume FROM solana.swaps a WHERE swap_from_mint = 'So11111111111111111111111111111111111111112' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp >= current_date - 30 GROUP BY date ORDER BY date ASC In this query we are ‘selecting’
The date_trunc function truncates the timestamp to a specific time value we want.
For example, if we truncate it to ‘day’ , all timestamp would lose their hour, minute and seconds values.
Given the timestamp ‘2022-01-01 12:00PM’ , a day date_trunc function would change this to: ‘2022-01-01 00:00’
The benefit of truncating it to ‘day’ is so that we can aggregate values like doing a sum so that we can see some trends in the data.
date_trunc('day', block_timestamp) hence, this means we are using the date truncate function on the column called block_timestamp and we want to truncate it by day.
there is a as day at the end which simply means we are naming this new column as ‘day’. We can then use ‘day’ to reference this new column.
sum(swap_from_amount) would sum all the rows from the column called swap_from_amount which we know is the amount of SOL being swapped In this query we will further filter by:
AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' to grab all transactions where SOL was swapped for USDC on Orca The results to our query should resemble:
• Since we truncated block_timestamp to display their day values only. We can see that all ‘DATE’ values have 00:00 after the ‘day’ value • And on the right, for each day , there is a corresponding volume denominated in SOL. Challenge 2: • Modify the above query to determine the volume of SOL being swapped for USDC during the time period of February 1 - February 28, 2022. Denominate the volume in terms of SOL. • Modify the above query to determine the volume of USDC being swapped for SOL during the time period of February 1 - February 28, 2022. Denominate the volume in terms of SOL.
Since we now have a queries for determining:
SOL to USDC swap volumes
USDC to SOL swap volumes Let’s put them both together to see if users on Orca have been buying or selling more SOL in the past 30 days To do so we’ll be utilizing our previously written queries and a Common Table Expression or CTE. A CTE will be useful for this query as it will enable us to combine various simple queries into a more complex one. The structure of our CTE will look like the following: WITH sol_in AS (SOL TO USDC VOLUME QUERY), sol_out AS (USDC TO SOL VOLUME QUERY) SELECT a.DATE, VOLUME_IN, VOLUME_OUT, VOLUME_IN - VOLUME_OUT AS FLOW FROM sol_in a LEFT JOIN sol_out b ON a.date = b.date ORDER BY a.date ASC In this mock query, we are creating a CTE that contains our SOL to USDC Volume query and the USDC to SOL Volume challenge query. Using our CTE, we can then use a LEFT JOIN to combine our two volume tables together and perform simple arithmetic operations to determine flow. In this case, flow is simply the daily volume of SOL being swapped into Orca minus the daily volume of SOL being swapped out of Orca. A positive flow indicates that there is more selling pressure than buying pressure and a negative flow will indicate the inverse. Now that we understand why we are using a CTE, let’s fill in the blanks with our queries. WITH sol_in AS ( SELECT date_trunc('day', block_timestamp) AS date, sum(swap_from_amount) AS volume_in FROM solana.swaps
WHERE swap_from_mint = 'So11111111111111111111111111111111111111112' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp >= current_date - 30 GROUP BY date ORDER BY date ASC ), sol_out AS ( SELECT date_trunc('day', block_timestamp) AS date, sum(swap_to_amount) AS volume_out FROM solana.swaps
WHERE swap_to_mint = 'So11111111111111111111111111111111111111112' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp >= current_date - 30 GROUP BY date ORDER BY date ASC)
SELECT a.date, a.volume_in, b.volume_out, a.volume_in - b.volume_out AS flow FROM sol_in a LEFT JOIN sol_out b ON a.date = b.date ORDER BY a.date ASC And the results from this query will look like:
Challenge 3: Fill in the CTE template with the Challenge 2: Volume Queries you created.
While this table is informative, a visual representation of the data may help us better understand what’s going on. Let’s create a visual representation of the data above using the ‘New Chart’ tab. We’ll create a bar chart with ‘DATE’ on the x-axis and ‘FLOW’ on the y-axis.
It was made evident, from the bar chart, that there has been more buying pressure than selling pressure on Orca in the past 30 days. Challenge 4: Create a visual representation of the data above using the ‘New Chart’ tab for the CTE you created. From your visualization does a clear pattern materialize? Have users on Orca been buying or selling more SOL from February 1 - February 28, 2022?
You’ve reached the end of this topic! 🎉 Congrats on reaching the end of this guide! Now it’s time for a challenge:
By using the solana.swaps table, create the same flow analysis for SOL-USDC swaps on Jupiter from February 1 - February 28, 2022 o Does the token flow on Orca follow a similar pattern as the token flow on Jupiter?
Once done, create a dashboard displaying the results for the challenge and submit it via https://flipsidecrypto.xyz/drops.
If you have any questions along the way or want to check the answer key, come join us at our dedicated discord channel for education bounties: https://discord.gg/3yAry49awp
WITH sol_in AS ( SELECT date_trunc('day', block_timestamp) AS date, sum(swap_from_amount) AS volume_in FROM SOLANA.FACT_SWAPS
WHERE swap_from_mint = 'So11111111111111111111111111111111111111112' AND swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp BETWEEN '2022-02-01' AND '2022-02-28' GROUP BY date ORDER BY date ASC ), sol_out AS ( SELECT date_trunc('day', block_timestamp) AS date, sum(swap_to_amount) AS volume_out FROM SOLANA.FACT_SWAPS
WHERE swap_to_mint = 'So11111111111111111111111111111111111111112' AND swap_from_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' AND succeeded = 'TRUE' AND swap_program = 'orca' AND block_timestamp BETWEEN '2022-02-01' AND '2022-02-28' GROUP BY date ORDER BY date ASC)
SELECT a.date, a.volume_in, b.volume_out, a.volume_in - b.volume_out AS flow FROM sol_in a LEFT JOIN sol_out b ON a.date = b.date ORDER BY a.date ASC;