SELECT

proposer as user_address,

max(date(block_timestamp)) AS last_tx_date

FROM

flow.core.fact_transactions

WHERE

TX_SUCCEEDED = true

GROUP BY

proposer

),

prev_last_tx AS (

SELECT

proposer as user_address,

max(date(block_timestamp)) AS prev_last_tx_date

FROM

flow.core.fact_transactions join last_tx

on proposer = user_address

WHERE

date(block_timestamp) < last_tx_date and

TX_SUCCEEDED = true

GROUP BY

proposer

),

user_retention AS (

SELECT

last_tx.user_address as user_address,

datediff('day', prev_last_tx_date, last_tx_date) AS retention_duration

FROM

last_tx join prev_last_tx

ON last_tx.user_address = prev_last_tx.user_address

SELECT

retention_duration,

COUNT(DISTINCT user_address) as "number of user addresses"