SELECT

sender,

DATE_TRUNC('week', block_timestamp) AS week,

MIN(block_timestamp) AS first_bridge_time

FROM

avalanche.defi.ez_bridge_activity

GROUP BY

sender, DATE_TRUNC('week', block_timestamp)

),

first_bridge_per_sender AS (

SELECT

sender,

MIN(DATE_TRUNC('week', first_bridge_time)) AS first_bridge_week

FROM

weekly_activity

GROUP BY

sender

),

weekly_bridger_trend AS (

SELECT

w.week,

w.sender,

CASE

WHEN w.week = f.first_bridge_week THEN 'new'

ELSE 'returning'

END AS bridger_status

FROM

weekly_activity w

JOIN

first_bridge_per_sender f

ON

w.sender = f.sender

)