# Flipside Crupto sol Bounty

By [Darknighte5a](https://paragraph.com/@darknighte5a) · 2022-06-07

---

flipsde crypto bounty
---------------------

![](https://storage.googleapis.com/papyrus_images/ab1d8d7333ac9b512072b94275f1c1468b3cdc289947e8d29cd64a63949e4d28.jpg)

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/](https://www.orca.so/) or read up it's docs here: [https://orca-so.medium.com/](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](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/](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:

1.  Since each row indicates the DEX, we can adjust our query to only consider swaps on Orca
    
2.  We are only looking at transactions where the token being swapped from is SOL
    
3.  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:

1.  USDC
    
2.  GST
    
3.  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:
    
4.  address\_name AS swapped\_to\_token
    
5.  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’

1.  The date\_trunc function truncates the timestamp to a specific time value we want.
    
2.  For example, if we truncate it to ‘day’ , all timestamp would lose their hour, minute and seconds values.
    
3.  Given the timestamp ‘2022-01-01 12:00PM’ , a day date\_trunc function would change this to: ‘2022-01-01 00:00’
    
4.  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.
    
5.  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.
    
6.  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.
    
7.  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:
    
8.  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:

1.  SOL to USDC swap volumes
    
2.  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:

1.  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?
    
2.  Once done, create a dashboard displaying the results for the challenge and submit it via [https://flipsidecrypto.xyz/drops](https://flipsidecrypto.xyz/drops).
    
3.  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](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;

---

*Originally published on [Darknighte5a](https://paragraph.com/@darknighte5a/flipside-crupto-sol-bounty)*