FLOW number of retention users

headitmanagerFLOW number of retention users

Updated 2022-07-16

with flow1 as (select distinct proposer from flow.core.fact_transactions where block_timestamp::date >= '2022-05-09' and block_timestamp::date < '2022-05-19')

,flow2 as (select distinct proposer from flow.core.fact_transactions where block_timestamp::date >= '2022-05-19' and block_timestamp::date < '2022-05-29')

,flowcount as (select count(*) as flowcount1 from flow1)

,flowretentioncount as (select count(*) as rcount from flow1 where proposer in (select proposer from flow2))

,flowpercent1 as (select (rcount/flowcount1*100) as percentage from flowcount,flowretentioncount)

,flow_number_of_trasactions as (select count(*) as c , proposer , case

when c=1 then 'one transactions'

when c=2 then 'two transactions'

when c=3 then 'three transactions'

when c=4 then 'four transactions'

when c=5 then 'five transactions'

when c=6 then 'six transactions'

when c=7 then 'seven transactions'

when c=8 then 'eigth transactions'

when c=9 then 'nine transactions'

when c=10 then 'ten transactions'

when c>10 then 'more than 10 transactions' end as transactions_count , 1

from flow.core.fact_transactions where

block_timestamp::date >= '2022-05-19' and block_timestamp::date < '2022-05-29' and proposer in (select proposer from flow1)

group by proposer )

, solana1 as (select distinct signers from solana.core.fact_transactions where block_timestamp::date >= '2022-05-09' and block_timestamp::date < '2022-05-19')

, solana2 as (select distinct signers from solana.core.fact_transactions where block_timestamp::date >= '2022-05-19' and block_timestamp::date < '2022-05-29')

,solanacount as (select count(*) as solanacount1 from solana1)

,solanaretentioncount as (select count(*) as rcount from solana1 where signers in (select signers from solana2))

,solanapercent as (select (rcount/solanacount1*100) as percentage from solanacount,solanaretentioncount)

,solana_number_of_trasactions as (select count(*) as c , signers , case