-- Get the first transaction date for each user

SELECT

from_address,

MIN(block_timestamp::DATE) AS first_tx_date

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

GROUP BY 1

, post_launch_users AS (

-- Get all users who transacted after the Kaia Wave

SELECT DISTINCT from_address, block_timestamp::DATE AS activity_date

FROM kaia.core.fact_transactions

WHERE tx_succeeded = 'TRUE'

AND block_timestamp::DATE >= '2024-09-25'

, new_users_daily AS (

-- New users per day (users whose first transaction was after the launch)

SELECT

p.activity_date,

COUNT(DISTINCT p.from_address) AS daily_new_users

FROM post_launch_users p

JOIN first_tx f ON p.from_address = f.from_address

WHERE f.first_tx_date = p.activity_date -- First transaction happens on the same day

GROUP BY 1

, returning_users_daily AS (

-- Returning users per day (users who had transactions before AND after the launch)

SELECT

p.activity_date,

COUNT(DISTINCT p.from_address) AS daily_returning_users

FROM post_launch_users p

JOIN first_tx f ON p.from_address = f.from_address