Flipside Challenge 01

    I just recently joined MetricsDAO and found this initial Flipside Challenge to get started with on-chain analytics. Although one is pretty well guided through the process of creating their first queries, visualizations and dashboards I had the feeling that there are already a lot of very specific SQL terms used which are not explained well enough in the tutorial. Therefore, the aim of this board is to guide a newbie through this first challenge in a way to maximize the knowledge one can take away from it. As a reference, here is the challenge: https://teamflipside.notion.site/Ethereum-1-Ethereum-Core-Table-Walkthough-Swaps-290ad820a4724c2b97425da1b948ed51

    Finding Swap Events

    Following query is used for finding the swap events:

    SELECT
       *
    FROM
       ETHEREUM.CORE.FACT_EVENT_LOGS
    WHERE
       block_timestamp >= CURRENT_DATE - 6
       AND contract_address = LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0') 
    -- this is the USDC-WETH SushiSwap Pool Address
       AND event_name IN ('Swap')
    

    Where I won't go into that much detail already, as these are really SQL basics and the items to be filtered for are well enough explained in the guide. One term although already caught my eye here, which we can dive deeper into:

    LOWER('0x397FF1542f962076d0BFE58eA045FfA2d347ACa0') 
    

    ❓What does this do?

    💡 LOWER() converts a string into lower case

    So the result of the upper statement for example looks like this: 0x397ff1542f962076d0bfe58ea045ffa2d347aca0

    ❓Why are we doing that?

    💡 Because in the ETHEREUM.CORE.FACT_EVENT_LOGS table all contract_addresses are stored in lower case.

    ❓Would it work without using the LOWER() function?

    ❌ NO, because it is case sensitive and the result would be 0 rows.

    Loading...
    So the result of this first query should look like this: