Arkanwallet 1
    Updated 2023-01-01
    with
    table1 AS (
    SELECT
    tx_signer as address,
    count(distinct tx_hash) as tx_counts,
    count(distinct date(block_timestamp)) as days_active,
    min(date(block_timestamp)) as first_tx,
    max(date(block_timestamp)) as last_tx,
    datediff('day', last_tx, getdate()) as days_last_active,
    datediff('day', first_tx, getdate()) as age_today
    from
    near.core.fact_transactions
    where block_timestamp >= '2022-01-01'
    group by 1
    ),
    table2 AS (
    SELECT
    first_tx,
    count(distinct address) as new_users,
    count(distinct case when days_active >= 7 AND days_last_active <= 7 then address else null end) as active_user,
    count(distinct case when days_active = 1 then address else null end) as one_day_user,
    count(distinct case when days_active >= 2 then address else null end) as two_or_more_days_user,
    count(distinct case when days_active >= 5 then address else null end) as five_or_more_days_user,
    count(distinct case when days_active >= 7 then address else null end) as seven_or_more_days_user,
    count(distinct case when days_active >= 10 then address else null end) as fourteen_or_more_days_user,
    count(distinct case when days_active >= 50 then address else null end) as fifty_or_more_days_user,
    count(distinct case when days_active >= 100 then address else null end) as hundred_or_more_days_user,
    count(distinct case when days_active >= 200 then address else null end) as two_hundred_or_more_days_user
    FROM
    table1
    WHERE tx_counts < 10000
    GROUP BY 1
    )
    SELECT
    first_tx,
    new_users as "New Wallets",
    Run a query to Download Data