SELECT tx_sender as address

, min(date_trunc('week',block_timestamp)) as cohortDate

FROM terra.core.fact_transactions

GROUP BY tx_sender

),

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('week',block_timestamp) as date

,count(distinct tx_sender) as total_players

FROM terra.core.fact_transactions

GROUP BY date

SELECT au.date

, nu.new_users_count

, au.total_players - nu.new_users_count AS Existing_Users

, (nu.new_users_count/au.total_players)*100 as New_User_Percentage

FROM all_users au

LEFT JOIN new_users nu

ON au.date = nu.date;