flipside

    total-tomato
    thejoycetotal-tomato
    Updated 2024-09-01
    WITH user_activity AS (
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,
    FROM_ADDRESS AS user,
    COUNT(DISTINCT TX_HASH) AS total_transactions
    FROM
    base.core.fact_transactions
    WHERE
    BLOCK_TIMESTAMP BETWEEN '2024-08-01' AND '2024-08-31'
    GROUP BY
    1, 2
    ),
    new_users AS (
    SELECT
    day,
    user
    FROM
    user_activity
    WHERE
    total_transactions = 1
    ),
    old_users AS (
    SELECT
    day,
    user
    FROM
    user_activity
    WHERE
    total_transactions > 1
    )
    SELECT
    ua.day,
    COUNT(DISTINCT new_users.user) AS new_users,
    COUNT(DISTINCT old_users.user) AS old_users
    FROM
    user_activity ua
    QueryRunArchived: QueryRun has been archived