SELECT

Date_trunc('day', block_timestamp) AS Date,

COUNT(DISTINCT from_address) as Users

FROM kaia.core.fact_transactions

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

GROUP BY 1

),

tb2 AS (

SELECT

min(block_timestamp::date) as Min_date,

from_address AS new_users

FROM kaia.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 Returning_Users,

new_users_cnt AS New_Users

FROM tb1 a

JOIN tb3 b ON a.Date = b.Min_date