SELECT

proposer as flow,min ( block_timestamp::date ) AS first_transaction

FROM flow.core.fact_transactions

GROUP BY flow

),

ALL_B AS (

SELECT proposer,min ( block_timestamp::date ) AS second_transaction

FROM flow.core.fact_transactions

JOIN ALL_A

ON proposer = flow

WHERE block_timestamp::date > first_transaction

GROUP BY proposer

),

ALL_C AS (

SELECT

proposer,datediff ( 'day', first_transaction, second_transaction ) AS transaction_duration

FROM ALL_B

JOIN ALL_A

ON proposer = flow

SELECT CASE

WHEN transaction_duration <= 7

THEN 'Transacted in the same Week'

WHEN transaction_duration > 7 AND transaction_duration <= 30

THEN 'Transacted in the same Month'

ELSE 'Transacted after a Month' End as retention_category,Count ( DISTINCT proposer ) as number_of_users

FROM ALL_C

GROUP BY retention_category