select

from_address,

min(block_timestamp::date) as min_date

from berachain.testnet.fact_transactions

where block_timestamp::date >= '2024-06-09' -- Berachain officially launched version 2 of its testnet called bArtio

group by from_address

),

new_user_counts as (

select

sum(case when min_date >= current_date - interval '1 day' then 1 else 0 end) as num_new_users_last_24_hours,

sum(case when min_date >= current_date - interval '7 days' then 1 else 0 end) as num_new_users_last_7_days,

sum(case when min_date >= current_date - interval '30 days' then 1 else 0 end) as num_new_users_last_30_days,

sum(case when min_date >= current_date - interval '90 days' then 1 else 0 end) as num_new_users_last_90_days,

count(from_address)/datediff(day, '2024-06-09', current_date) as average_rate_of_new_user_per_day

from users

select * from new_user_counts;