payer,

min(block_timestamp::date) as first_tx_date,

max(block_timestamp::date) as last_tx_date,

datediff('day', first_tx_date, last_tx_date) as retention_duration

from flow.core.fact_transactions

group by 1),

eth_ret as (select

from_address,

min(block_timestamp::date) as first_tx_date,

max(block_timestamp::date) as last_tx_date,

datediff('day', first_tx_date, last_tx_date) as retention_duration

from ethereum.core.fact_transactions

group by 1),

sol_ret as (select

signers[0] as signer,

min(block_timestamp::date) as first_tx_date,

max(block_timestamp::date) as last_tx_date,

datediff('day', first_tx_date, last_tx_date) as retention_duration

from solana.core.fact_transactions

group by 1),

flow as (select case

when retention_duration >= 1 and retention_duration <= 7 then 'less than One week'

when retention_duration > 7 and retention_duration <= 31 then 'Between on week and one month'

when retention_duration > 31 and retention_duration <= 60 then 'Between one or two month'

when retention_duration > 60 and retention_duration <= 365 then 'Between two month and one year'

when retention_duration > 365 then 'More than one year'

end as type,

payer

from flow_ret),

eth as (select case

when retention_duration < 1 then 'Just in one day'