flipside

    2) bitcoin acquired users
    Flipside Community2) bitcoin acquired users
    Updated 2023-12-07
    with t1 as (select
    PUBKEY_SCRIPT_ADDRESS as address,
    count(distinct tx_id) as n_tx
    from bitcoin.core.fact_outputs
    group by 1
    )

    select address
    from t1
    where n_tx >= 2 -- acquired users aka came back for at least a second transaction

    -- addresses with tx
    -- with first_transaction as
    -- (
    -- select PUBKEY_SCRIPT_ADDRESS, min(block_timestamp) as first_time
    -- from bitcoin.core.fact_outputs
    -- group by PUBKEY_SCRIPT_ADDRESS
    -- )

    -- select substr(date_trunc('year',first_time),0,4) as year, count(distinct PUBKEY_SCRIPT_ADDRESS) as Number_of_users, sum(Number_of_users) over (order by year asc) as cummulative_users
    -- from first_transaction
    -- group by year
    -- order by year desc
    Run a query to Download Data