Popex404Cumulative Cosmos
    Updated 2023-02-19
    with user_cohorts as (
    SELECT
    tx_from as address,
    min(date_trunc('day',block_timestamp)) as cohortDate
    FROM cosmos.core.fact_transactions
    where tx_succeeded = true
    GROUP BY 1
    ),
    new_users as (
    SELECT cohortDate as date, count (distinct address) as new_users_count
    FROM user_cohorts uc
    Group by Date
    ),
    all_users as (
    Select date_trunc('day',block_timestamp) as date,
    count (distinct tx_from) as total_users
    FROM cosmos.core.fact_transactions
    where tx_succeeded = true
    Group by 1
    ),
    transactions as (
    select date_trunc('day',block_timestamp) as datetx,
    count (distinct tx_id) as txs
    FROM cosmos.core.fact_transactions
    where tx_succeeded = true
    Group by 1
    )
    SELECT nu.date as "Date",
    sum(nu.new_users_count) over (order by nu.date) as "Cumulative New Users",
    sum(tx.txs) over (order by nu.date) as "Cumulative Transactions"
    FROM new_users nu
    LEFT JOIN transactions tx
    ON nu.date = tx.datetx
    Run a query to Download Data