SELECT

USER_ADDRESS as address,

min(date_trunc('day',LAST_ACTIVITY_BLOCK_TIMESTAMP)) as cohortDate

FROM ethereum.core.ez_current_balances

where symbol='DYDX' and token_name='dYdX'

and user_address not in (select address from ethereum.core.dim_labels where address_name is not null)

and current_bal is not null and current_bal >0

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',LAST_ACTIVITY_BLOCK_TIMESTAMP) as date,

count (distinct USER_ADDRESS) as total_users

FROM ethereum.core.ez_current_balances

where symbol='DYDX' and token_name='dYdX'

and user_address not in (select address from ethereum.core.dim_labels where address_name is not null)

and current_bal is not null and current_bal >0

Group by 1

SELECT au.date as "Date",

nu.new_users_count as "New Holders",

sum("New Holders") over (Order by "Date") as "Cumulative New Holders",

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

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

FROM all_users au

LEFT JOIN new_users nu

ON au.date = nu.date