SELECT

BLOCK_TIMESTAMP::date AS date,

CASE

WHEN DECODED_LOG:from IS NOT NULL THEN DECODED_LOG:from

WHEN DECODED_LOG:to IS NOT NULL THEN DECODED_LOG:to

END AS user_address

FROM avalanche.core.ez_decoded_event_logs

WHERE ORIGIN_TO_ADDRESS IN (

'0xd0996694bc687a3c3e5de6332e6e542ad774dd3f',

'0x8d904c8b6bb673d88cdbcd5798cda439f33cb3a4'

)

AND TX_STATUS = 'SUCCESS'

),

user_first_seen AS (

-- Look at entire history to properly identify first interaction

SELECT

user_address,

MIN(date) AS first_active_date

FROM all_user_activity

WHERE user_address NOT IN (

'0xd0996694bc687a3c3e5de6332e6e542ad774dd3f',

'0x8d904c8b6bb673d88cdbcd5798cda439f33cb3a4'

)

AND user_address IS NOT NULL

GROUP BY user_address

),

daily_metrics AS (

SELECT

a.date,

COUNT(DISTINCT a.user_address) as total_users,

COUNT(DISTINCT CASE

WHEN a.date = f.first_active_date THEN a.user_address

ELSE NULL