zyroqusers 2
    Updated 2024-12-16
    WITH first_time_users AS (
    SELECT
    CONTRACT_ADDRESS,
    ORIGIN_FROM_ADDRESS,
    MIN(BLOCK_TIMESTAMP) AS first_transaction_time
    FROM
    avalanche.core.fact_event_logs
    GROUP BY
    CONTRACT_ADDRESS, ORIGIN_FROM_ADDRESS
    HAVING
    MIN(BLOCK_TIMESTAMP) >= '2024-12-16 17:00:00.000' -- First transaction within the timeframe
    ),
    user_activity_by_type AS (
    SELECT
    f.CONTRACT_ADDRESS,
    f.ORIGIN_FROM_ADDRESS,
    labels.LABEL_TYPE AS type
    FROM
    first_time_users f
    INNER JOIN
    avalanche.core.dim_labels labels
    ON
    f.CONTRACT_ADDRESS = labels.ADDRESS
    ),
    new_user_count_by_type AS (
    SELECT
    type,
    COUNT(DISTINCT ORIGIN_FROM_ADDRESS) AS new_user_count
    FROM
    user_activity_by_type
    GROUP BY
    type
    )
    SELECT
    type,
    new_user_count