SELECT

block_timestamp,

DATE_TRUNC('quarter', block_timestamp ) AS quarter,

TX_SIGNER

FROM near.core.fact_transactions

WHERE block_timestamp BETWEEN DATEADD(quarter, -6, CURRENT_DATE) AND CURRENT_DATE

AND TX_STATUS = 'Success'

GROUP BY 1, 2, 3

),

cohort_size AS (

SELECT

quarter,

COUNT(DISTINCT TX_SIGNER) AS quarter_cohort_size

FROM quarter_cohort

GROUP BY 1

),

retention AS (

SELECT

quarter_cohort.quarter,

cohort_size.quarter_cohort_size,

DATE_TRUNC('month', DATEADD(month, 1, quarter_cohort.quarter)) AS month1,

DATE_TRUNC('month', DATEADD(month, 2, quarter_cohort.quarter)) AS month2,

DATE_TRUNC('month', DATEADD(month, 3, quarter_cohort.quarter)) AS month3,

DATE_TRUNC('month', DATEADD(month, 4, quarter_cohort.quarter)) AS month4,

DATE_TRUNC('month', DATEADD(month, 5, quarter_cohort.quarter)) AS month5,

DATE_TRUNC('month', DATEADD(month, 6, quarter_cohort.quarter)) AS month6,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN quarter_cohort.quarter AND DATEADD(month, 1, quarter_cohort.quarter) THEN TX_SIGNER END) AS month1_retention,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN DATEADD(month, 1, quarter_cohort.quarter) AND DATEADD(month, 2, quarter_cohort.quarter) THEN TX_SIGNER END) AS month2_retention,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN DATEADD(month, 2, quarter_cohort.quarter) AND DATEADD(month, 3, quarter_cohort.quarter) THEN TX_SIGNER END) AS month3_retention,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN DATEADD(month, 3, quarter_cohort.quarter) AND DATEADD(month, 4, quarter_cohort.quarter) THEN TX_SIGNER END) AS month4_retention,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN DATEADD(month, 4, quarter_cohort.quarter) AND DATEADD(month, 5, quarter_cohort.quarter) THEN TX_SIGNER END) AS month5_retention,

COUNT(DISTINCT CASE WHEN block_timestamp BETWEEN DATEADD(month, 5, quarter_cohort.quarter) AND DATEADD(month, 6, quarter_cohort.quarter) THEN TX_SIGNER END) AS month6_retention

FROM quarter_cohort

JOIN cohort_size ON quarter_cohort.quarter = cohort_size.quarter

GROUP BY 1, 2, 3, 4, 5, 6, 7, 8