SELECT

DATE_TRUNC('{{granularity }}', BLOCK_TIMESTAMP) AS date,

CASE

WHEN direction = 'inbound' THEN destination_address

ELSE source_address

END AS user_address,

FROM near.defi.ez_bridge_activity

WHERE

platform = 'rainbow'

AND receipt_succeeded = 1

AND BLOCK_TIMESTAMP > CURRENT_DATE - INTERVAL '{{trading_period}} days'

GROUP BY 1, 2

),

user_status AS (

SELECT

CASE

WHEN direction = 'inbound' THEN destination_address

ELSE source_address

END AS user_address,

MIN(DATE_TRUNC('{{granularity }}', BLOCK_TIMESTAMP)) AS first_tx_date

FROM near.defi.ez_bridge_activity

WHERE BLOCK_TIMESTAMP > CURRENT_DATE - INTERVAL '{{trading_period}} days'

GROUP BY user_address

SELECT

ua.date,

COUNT(DISTINCT ua.user_address) AS AU, -- Active Users

COUNT(DISTINCT CASE

WHEN us.first_tx_date = ua.date THEN ua.user_address

END) AS new_users, -- New Users

COUNT(DISTINCT CASE

WHEN us.first_tx_date < ua.date THEN ua.user_address

END) AS returning_users -- Returning Users