nickpayiatis_2023-03-02 10:54 AM copy
    Updated 2023-03-02
    -- forked from 8f2af39f-126b-4029-9564-a1de1fe77de0

    WITH quarter_cohort AS (
    SELECT
    block_timestamp,
    DATE_TRUNC('quarter', block_timestamp::date) AS quarter,
    TX_SIGNER
    FROM near.core.fact_transactions
    WHERE block_timestamp::date 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::date BETWEEN quarter_cohort.quarter AND DATEADD(month, 1, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month1_retention,
    COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 1, quarter_cohort.quarter)::date AND DATEADD(month, 2, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month2_retention,
    COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 2, quarter_cohort.quarter)::date AND DATEADD(month, 3, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month3_retention,
    COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 3, quarter_cohort.quarter)::date AND DATEADD(month, 4, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month4_retention,
    COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 4, quarter_cohort.quarter)::date AND DATEADD(month, 5, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month5_retention,
    COUNT(DISTINCT CASE WHEN block_timestamp::date BETWEEN DATEADD(month, 5, quarter_cohort.quarter)::date AND DATEADD(month, 6, quarter_cohort.quarter)::date - 1 THEN TX_SIGNER END) AS month6_retention
    FROM quarter_cohort
    Run a query to Download Data