SELECT

block_timestamp,

FROM_ADDRESS,

tx_hash

FROM ink.core.fact_transactions

,

active_users_u as (

SELECT

date_trunc('day', block_timestamp) as date,

count(DISTINCT FROM_ADDRESS) as active_users

FROM eventTb

GROUP BY date

, new as (

SELECT

date_trunc('day', first_tx) as date,

count(DISTINCT user) as new_user

FROM (

SELECT

FROM_ADDRESS as user,

min(block_timestamp) as first_tx

FROM eventTb

GROUP BY 1

GROUP BY 1

SELECT

a.date,

active_users,

coalesce(new_user,0) as n_new_user,

sum(n_new_user) over (ORDER by date) as cum_users,

active_users - n_new_user as recurring_user,

100 * n_new_user / active_users as new_user_percent,