theericstonebase 3-month cohort retention
    Updated 2024-04-04
    -- forked from base 3-month cohort retention copy @ https://flipsidecrypto.xyz/edit/queries/1daac2a0-e098-4d7a-911e-fa5a700a3ca6

    -- forked from base 3-month cohort retention @ https://flipsidecrypto.xyz/edit/queries/cae77fee-d3f8-4d94-83d9-8abb7faaafe7

    WITH monthly_activity AS (
    SELECT
    DATE_TRUNC('month', block_timestamp) AS activity_month,
    from_address,
    COUNT(DISTINCT tx_hash) AS transaction_count
    FROM
    base.core.fact_transactions
    WHERE
    block_timestamp >= CURRENT_DATE() - INTERVAL '1.5 years'
    AND from_address NOT IN (
    -- Exclude contract addresses
    SELECT address FROM base.core.dim_contracts
    UNION
    SELECT address from base.core.dim_labels
    -- Add additional subqueries to exclude centralized exchanges or protocols
    -- For example, using dim_labels or dim_tags if available
    )
    GROUP BY
    activity_month,
    from_address
    HAVING
    COUNT(DISTINCT tx_hash) >= 2
    ),

    cohorts AS (
    SELECT
    activity_month,
    from_address
    FROM
    monthly_activity
    ),

    QueryRunArchived: QueryRun has been archived