WITH transfer_data AS (

SELECT DISTINCT

BLOCK_TIMESTAMP,

FACT_TRANSACTIONS_ID AS tx_id,

PAYER

FROM flow.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '30 DAYS'

),

user_activity AS (

SELECT

PAYER AS user_address,

DATE(BLOCK_TIMESTAMP) AS activity_day,

MIN(DATE(BLOCK_TIMESTAMP)) OVER (PARTITION BY PAYER) AS first_activity_day,

DATEDIFF(

'day',

MIN(DATE(BLOCK_TIMESTAMP)) OVER (PARTITION BY PAYER),

DATE(BLOCK_TIMESTAMP)

) AS day_difference

FROM transfer_data

),

new_users AS (

SELECT

first_activity_day,

COUNT(DISTINCT user_address) AS new_user_count

FROM user_activity

WHERE day_difference = 0 -- Only considering the first activity day as new user day

GROUP BY first_activity_day

),

returning_users AS (

SELECT

first_activity_day,

day_difference,

COUNT(DISTINCT user_address) AS returning_user_count

FROM user_activity