main as (

tx_hash,

block_timestamp,

iff(direction = 'inbound', destination_address, source_address) as user,

symbol,

amount_usd,

source_chain,

destination_chain

from near.defi.ez_bridge_activity

where platform = 'rainbow'

and receipt_succeeded = 1

and block_timestamp between '{{ start_date }}' and '{{ end_date }}'

),

user_activity AS (

SELECT

user,

date_trunc('{{ period }}', block_timestamp) AS date_start

FROM main

GROUP BY user, date_start

a.date_start AS start_date,

COUNT(DISTINCT a.user) AS active_users,

COUNT(DISTINCT b.user) AS retained_users,

(COUNT(DISTINCT b.user) * 100.0 / COUNT(DISTINCT a.user)) AS retention_rate

FROM user_activity a

LEFT JOIN user_activity b

ON a.user = b.user

AND a.date_start = b.date_start + INTERVAL '1 {{ period }}'

GROUP BY a.date_start

ORDER BY a.date_start