flipside

    Engaged Wallets- def.2
    SocioCryptoEngaged Wallets- def.2
    Updated 2023-09-12
    -- forked from Active Wallets @ https://flipsidecrypto.xyz/edit/queries/ee1cff8f-5ee3-441b-a751-9082ea03fa78

    -- Active users across the chain,
    -- Active Users Definition 2: Wallets with at least three days executed transactions in the past months.
    -- Wallets who were active at least three days in the past month.

    with activities as (
    SELECT from_address,
    date_trunc('day',block_timestamp) as date
    --rank()over(partition by from_address order by date) as rank_date
    FROM avalanche.core.fact_transactions
    WHERE status = 'SUCCESS'
    GROUP BY 1,2
    ),
    active_wallets as (
    SELECT date_trunc('month',date) as month,
    from_address,
    count(date) as n_txn
    FROM activities
    GROUP BY 1 ,2
    HAVING n_txn >=4
    )

    SELECT month,
    count(DISTINCT from_address) n_engaged_wallets
    FROM active_wallets
    GROUP by 1
    Run a query to Download Data