select

signers[0] as wallets,

Min(date_trunc('day', block_timestamp)) as min_date,

Max(date_trunc('day', block_timestamp)) as max_date,

count(distinct tx_id) as "Transaction Numbers"

from solana.core.fact_transactions

where block_timestamp >= CURRENT_DATE - 90

group by 1

),

tb2 as (

select

wallets,

avg(datediff('day', min_date, max_date))/avg("Transaction Numbers") as "Time Between Transactions"

from tb1

group by 1

)

select

Case

when "Time Between Transactions" < 1 then '(1) Less Than a Day'

when "Time Between Transactions" between 1 and 7 then '(2) Between 1 day and 1 week'

when "Time Between Transactions" between 8 and 30 then '(3) Between 1 week and 1 month'

when "Time Between Transactions" > 30 then '(4) More than 1 month' END as "Transactions Time Between",

count(*) as "Count of Wallets"

from tb2

where "Transactions Time Between" is not null

group by 1

order by 1