SELECT FROM_ADDRESS as address

, min(block_timestamp::date) as cohortDate

FROM ethereum.core.fact_transactions

Where 1=1

AND TO_ADDRESS = lower('{{token_address}}')

AND STATUS = 'SUCCESS'

GROUP BY address

),

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 block_timestamp::date as date

,count(distinct FROM_ADDRESS) as total_players

FROM ethereum.core.fact_transactions

Where 1=1

AND TO_ADDRESS = lower('{{token_address}}')

AND STATUS = 'SUCCESS'

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;