-- forked from Time based Cohort Wide copy @ https://flipsidecrypto.xyz/edit/queries/b47e0739-b3bf-473e-aa3a-55b074804e8e

-- forked from 0xDataWolf / Time based Cohort Wide @ https://flipsidecrypto.xyz/0xDataWolf/q/jEC6M61jinzx/time-based-cohort-wide

with dauTb as (

SELECT

block_timestamp,

signers[0] as dau

FROM solana.core.fact_events

WHERE

program_id = 'SPoo1Ku8WFXoNDMHPsrGSTSG1Y47rzgn41SLUNakuHy'

AND instruction :accounts [0] :: STRING IN (

-- jito stake pool

'Jito4APyf642JPZPx3hGc6WWJ8zPKtRbRs4P815Awbb'

)

and BLOCK_TIMESTAMP >= '2022-10-06'

and BLOCK_TIMESTAMP <= '2023-10-06'

, base_table as ( -- this is data prep

select

dau as address

, date_trunc('month', block_timestamp) as date

, min(date_trunc('month', block_timestamp)) over(partition by address) as earliest_date

, datediff(

'month'

, min(date_trunc('month', block_timestamp)) over(partition by address) -- earliest_date

, date_trunc('month', block_timestamp) -- current date in month

) as difference

from dauTb

-- where BLOCK_TIMESTAMP >= '2022-10-06'

-- and BLOCK_TIMESTAMP <= '2023-09-30'

HAVING date_trunc('month', block_timestamp) < date_trunc('month', current_date())