SocioAnalyticaNew and Returning Users copy
    Updated 2025-02-09
    with DAU_u as (
    SELECT
    date_trunc('d', block_timestamp) as date,
    count(DISTINCT origin_from_address) as DAU
    FROM arbitrum.core.ez_decoded_event_logs
    WHERE contract_address IN (SELECT address FROM arbitrum.core.dim_labels where project_name ilike '%beefy%')
    AND date >= dateadd('month', -12, current_date)
    GROUP BY date
    )

    ,new as (
    SELECT
    date_trunc('d', first_tx) as date,
    count(DISTINCT user) as new_user
    FROM (
    SELECT
    origin_from_address as user,
    min(block_timestamp) as first_tx
    FROM arbitrum.core.ez_decoded_event_logs
    WHERE contract_address IN (SELECT address FROM arbitrum.core.dim_labels where project_name ilike '%beefy%')
    AND block_timestamp::DATE >= '2021-07-22'
    GROUP BY 1)
    WHERE date >= dateadd('month', -12, current_date)
    GROUP BY 1
    )

    SELECT
    a.date,
    Dau,
    coalesce(new_user,0) as n_new_user,
    Dau - n_new_user as recurring_user,
    100 * n_new_user / Dau as new_user_percent,
    100 * recurring_user / Dau as recurring_user_percent
    FROM DAU_u a
    LEFT JOIN new b using(date)
    ORDER BY 1 DESC
    QueryRunArchived: QueryRun has been archived