nsa2000osmo met2
    Updated 2023-02-11
    with new_osmosis_users AS (
    SELECT
    tx_from as address,
    count(distinct tx_id) 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 osmosis.core.fact_transactions
    group by 1
    ),
    new_users AS (
    SELECT
    first_tx,
    count(distinct address) as "New Users",
    sum("New Users") over (order by first_tx) as "Cumulative New"
    FROM new_osmosis_users
    GROUP BY 1
    ),
    active_users AS (
    SELECT
    date(block_timestamp) as date,
    count(distinct tx_from) as "Distinct Wallets",
    count(distinct tx_id) as tx_counts
    FROM osmosis.core.fact_transactions
    where date >='2023-01-01'
    GROUP BY 1
    )
    SELECT
    *
    FROM active_users
    LEFT JOIN new_users ON first_tx = date
    ORDER BY 1 DESC
    Run a query to Download Data