rajsFlow New and Active Users
    Updated 2022-07-19
    with new_users as
    (
    SELECT
    joined_date as date,
    count(distinct user) as new_users
    FROM
    (
    SELECT
    authorizers[0] as user,
    min(date_trunc('week', block_timestamp)) as joined_date
    from flow.core.fact_transactions
    where block_timestamp >= '2022-04-01'
    group by 1
    -- limit 3
    )
    group by 1
    )
    ,

    active_users as
    (
    SELECT
    date_trunc('week', block_timestamp) as date,
    count(distinct authorizers[0]) as active_users
    from flow.core.fact_transactions
    where block_timestamp >= '2022-04-01'
    group by 1
    )

    SELECT
    a.date,
    active_users,
    new_users,
    new_users / active_users * 100 as new_users_pct,
    active_users - new_users as returning_users,
    (active_users - new_users) / active_users * 100 as returning_users_pct
    Run a query to Download Data