-- First get all user activity from both chains with daily granularity

SELECT

CAST(value AS VARCHAR) AS user_address,

DATE_TRUNC('day', b.block_timestamp) AS activity_day

FROM

flow.core.ez_transaction_actors AS b,

LATERAL FLATTEN(INPUT => b.actors) AS a

WHERE

block_timestamp >= DATEADD(month, -4, CURRENT_DATE)

UNION ALL

SELECT

from_address AS user_address,

DATE_TRUNC('day', block_timestamp) AS activity_day

FROM

flow.core_evm.fact_transactions

WHERE

block_timestamp >= DATEADD(month, -4, CURRENT_DATE)

),

user_first_day AS (

-- Get each user's first day of activity

SELECT

user_address,

MIN(activity_day) AS first_activity_day

FROM user_activity

GROUP BY 1

),

base_cohort_size AS (

SELECT

first_activity_day,

COUNT(DISTINCT user_address) as cohort_size

FROM user_first_day