Zanyar_98Number of Sushiswap users and Number of new Optimism users - Weekly chart
    Updated 2023-04-06
    WITH New_Users_First_Transaction_TimeStamp AS (
    SELECT MIN(BLOCK_TIMESTAMP) AS "First Transaction", ORIGIN_FROM_ADDRESS "User Address"
    FROM optimism.core.fact_event_logs
    GROUP BY "User Address"
    HAVING "First Transaction"::DATE < CURRENT_DATE - 1
    ORDER BY "First Transaction"
    ),
    New_Users_Transactions AS (
    SELECT Block_Timestamp, TX_HASH, "User Address"
    FROM New_Users_First_Transaction_TimeStamp JOIN optimism.core.fact_event_logs ON ("User Address" = ORIGIN_FROM_ADDRESS)
    GROUP BY Block_Timestamp, TX_HASH, "User Address"
    ORDER BY Block_Timestamp
    ),

    Rank_Transactions AS (
    SELECT BLOCK_TIMESTAMP, TX_HASH, "User Address",
    RANK() OVER (Partition By "User Address" ORDER by BLOCK_TIMESTAMP ASC ROWS BETWEEN unbounded preceding AND CURRENT ROW) "Transactions Order"
    FROM New_Users_Transactions ORDER BY BLOCK_TIMESTAMP ASC
    ),
    New_Users_First_Two_Transactions AS (
    SELECT BLOCK_TIMESTAMP, TX_HASH, "User Address", "Transactions Order"
    FROM Rank_Transactions
    WHERE "Transactions Order" < 3
    ),

    SWAPS AS (
    SELECT BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FROM_ADDRESS AS "User Address", 'Swap' AS TYPE
    FROM optimism.sushi.ez_swaps
    GROUP BY BLOCK_TIMESTAMP, TX_HASH, ORIGIN_FROM_ADDRESS
    ORDER BY BLOCK_TIMESTAMP
    ),

    XSWAPS AS (
    SELECT BLOCK_TIMESTAMP,TX_HASH, ORIGIN_FROM_ADDRESS AS "User Address", 'xSwap' AS TYPE
    FROM optimism.core.fact_event_logs WHERE ORIGIN_TO_ADDRESS = '0x8b396ddf906d552b2f98a8e7d743dd58cd0d920f'
    Run a query to Download Data