MIN(DATE(block_timestamp)) as first_date,

from_address

from polygon.core.fact_transactions

group by 2),

newUsers as (select

first_date,

count(distinct from_address) as new_users

from newWallet

group by 1 ),

existactiveusers as (select

DATE(block_timestamp) as dayz,

count (distinct t.from_address) as Users,

new_users,

users - coalesce(new_users, 0) as existing_users

from polygon.core.fact_transactions t

left join newUsers ON first_date = t.block_timestamp::date

group by 1 , 3

having dayz >= '2023-01-01' AND dayz < CURRENT_DATE)

select

dayz,

case when dayz = '2023-01-17' then 'Polygon Hard Fork Day'

when dayz < '2023-01-17' then 'Pre-Fork Day'

when dayz > '2023-01-17' then 'Post-Fork Day'

end as day_distribution,

new_users,

existing_users,

sum(new_users) over (partition by day_distribution) as new_users_dist,

sum(existing_users) over (partition by day_distribution) as existing_active_users_dist

from existactiveusers