zakkisyedUniswap: New users in April - Crosschain
    Updated 2023-05-14
    WITH base_table AS (
    SELECT
    TRADER,
    BLOCKCHAIN,
    BLOCK_TIMESTAMP::DATE AS TX_DATE,
    MIN(BLOCK_TIMESTAMP::DATE) OVER (PARTITION BY TRADER) AS FIRST_TX_DATE
    FROM crosschain.core.ez_dex_swaps
    WHERE PLATFORM LIKE 'uniswap%'
    AND BLOCK_TIMESTAMP >= '2023-04-01' AND BLOCK_TIMESTAMP < '2023-05-01'
    ),

    daily_new_users AS (
    SELECT
    BLOCKCHAIN,
    TX_DATE,
    COUNT(DISTINCT CASE WHEN TX_DATE = FIRST_TX_DATE THEN TRADER END) AS DAILY_NEW_USER_COUNT
    FROM base_table
    GROUP BY BLOCKCHAIN, TX_DATE
    ),

    cumulative_new_users AS (
    SELECT
    BLOCKCHAIN,
    TX_DATE,
    SUM(DAILY_NEW_USER_COUNT) OVER (PARTITION BY BLOCKCHAIN ORDER BY TX_DATE) AS CUMULATIVE_USER_COUNT
    FROM daily_new_users
    ),

    daily_transactions AS (
    SELECT
    BLOCKCHAIN,
    TX_DATE,
    COUNT(*) AS DAILY_TX_COUNT
    FROM base_table
    GROUP BY BLOCKCHAIN, TX_DATE
    ),
    Run a query to Download Data