SELECT

Date_trunc('day', block_timestamp) AS Date,

COUNT(DISTINCT authorizers[0]) as Users

FROM flow.core.fact_transactions

WHERE block_timestamp::date >= '2024-01-01'

GROUP BY 1

),

tb2 AS (

SELECT

min(block_timestamp::date) as Min_date,

authorizers[0] AS new_users

FROM flow.core.fact_transactions

GROUP BY 2

),

tb3 AS (

SELECT

Min_date,

COUNT(DISTINCT new_users) as new_users_cnt

FROM tb2

WHERE Min_date >= '2024-01-01'

GROUP BY 1

SELECT

Date,

Users-new_users_cnt AS Active_Users,

new_users_cnt AS "NEW USERS"

FROM tb1 a

JOIN tb3 b ON a.Date = b.Min_date