SELECT

from_address,

MIN(block_timestamp) AS first_transaction_date

FROM

kaia.core.fact_transactions

GROUP BY

from_address

),

classified_transactions AS (

SELECT

t.tx_hash,

t.from_address,

t.block_timestamp,

CASE

WHEN t.block_timestamp = u.first_transaction_date THEN 'New User'

ELSE 'Old User'

END AS user_type

FROM

kaia.core.fact_transactions t

JOIN

user_first_transaction u

ON

t.from_address = u.from_address

SELECT

trunc(block_timestamp,'week') AS transaction_date,

user_type,

count(DISTINCT from_address) as users

FROM

classified_transactions

where block_timestamp::date >= '2022-01-01'

GROUP BY

1,2

ORDER BY

1,2