SELECT

PROVIDER_ADDRESS,

MIN(BLOCK_TIMESTAMP) AS first_interaction

FROM solana.marinade.ez_liquid_staking_actions

WHERE BLOCK_TIMESTAMP >= DATEADD('day', -180, CURRENT_DATE)

GROUP BY PROVIDER_ADDRESS

),

daily_activity AS (

SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS day,

PROVIDER_ADDRESS

FROM solana.marinade.ez_liquid_staking_actions

WHERE BLOCK_TIMESTAMP >= DATEADD('day', -180, CURRENT_DATE)

GROUP BY 1, 2

),

cohort_status AS (

SELECT

d.day,

d.PROVIDER_ADDRESS,

f.first_interaction,

CASE

WHEN f.first_interaction >= DATEADD('day', -7, d.day) THEN 'New'

WHEN f.first_interaction < DATEADD('day', -30, d.day) THEN 'Retained'

ELSE 'Inactive'

END AS user_status

FROM daily_activity d

LEFT JOIN first_tx f ON d.PROVIDER_ADDRESS = f.PROVIDER_ADDRESS

SELECT

day,

user_status,

COUNT(DISTINCT PROVIDER_ADDRESS) AS address_count

FROM cohort_status

GROUP BY 1, 2

ORDER BY day DESC, user_status;