SELECT

from_address

, MAX(DATE_TRUNC('quarter', block_timestamp)) AS last_active_quarter

FROM kaia.core.fact_transactions

GROUP BY 1

),

all_users AS ( -- Get all users who were active in each quarter

SELECT

DATE_TRUNC('quarter', block_timestamp) AS active_quarter

, from_address

FROM kaia.core.fact_transactions

),

churned_users AS ( -- Users who were last active in a quarter and never returned

SELECT

l.last_active_quarter AS churned_quarter

, COUNT(DISTINCT l.from_address) AS churned_users

FROM last_activity l

LEFT JOIN all_users a

ON l.from_address = a.from_address

AND a.active_quarter > l.last_active_quarter -- Check if they were active later

WHERE a.from_address IS NULL -- If NULL, they never returned

GROUP BY 1

-- Get total users per quarter and join with churned users

SELECT

a.active_quarter AS "Quarter"

, COUNT(DISTINCT a.from_address) AS "Total Users"

, COALESCE(c.churned_users, 0) AS "Churned Users"

FROM all_users a

LEFT JOIN churned_users c

ON a.active_quarter = c.churned_quarter

WHERE a.active_quarter < DATE_TRUNC('quarter', CURRENT_DATE) -- Exclude the current quarter

GROUP BY 1, 3

ORDER BY 1