i_danKaia: New Vs Returning Users
    Updated 2025-02-26
    WITH first_tx AS (
    SELECT
    from_address
    , MIN(block_timestamp) AS first_quarter
    FROM kaia.core.fact_transactions
    GROUP BY 1
    ),
    user_activity AS (
    SELECT
    t.from_address
    , DATE_TRUNC('quarter', t.block_timestamp) AS activity_quarter
    , DATE_TRUNC('quarter', f.first_quarter) AS first_tx_quarter
    FROM kaia.core.fact_transactions t
    JOIN first_tx f ON t.from_address = f.from_address
    )
    SELECT
    activity_quarter AS "Quarter"
    , COUNT(DISTINCT CASE WHEN activity_quarter = first_tx_quarter THEN from_address END) AS "New Users"
    , COUNT(DISTINCT CASE WHEN activity_quarter > first_tx_quarter THEN from_address END) AS "Returning Users"
    , COUNT(DISTINCT from_address) AS "Quarterly Users" --
    FROM user_activity
    GROUP BY 1
    ORDER BY 1


    QueryRunArchived: QueryRun has been archived