SELECT

tx_from as address,

min(date_trunc('month',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('month',block_timestamp) as date,

count (distinct tx_from) as total_users

FROM cosmos.core.fact_transactions

where tx_succeeded = true

Group by 1

SELECT au.date as "Date",

nu.new_users_count as "New Users",

au.total_users - nu.new_users_count AS "Existing Users",

(nu.new_users_count/au.total_users)*100 as "New User Percentage"

FROM all_users au

LEFT JOIN new_users nu

ON au.date = nu.date