flipside

    New Users Sector
    feyikemiNew Users Sector
    Updated 2025-02-25
    WITH first_time_users AS (
    SELECT
    from_address,
    MIN(block_timestamp::DATE) AS first_tx_date
    FROM kaia.core.fact_transactions
    WHERE tx_succeeded = 'TRUE'
    GROUP BY 1
    )

    , sector_activity AS (
    SELECT
    Date_trunc('day', Block_timestamp) AS Date,
    b.label_type AS sector,
    -- b.Project_name,
    COUNT(DISTINCT a.tx_hash) AS total_txns,
    COUNT(DISTINCT a.from_address) AS total_users,
    COUNT(DISTINCT CASE
    WHEN f.first_tx_date >= '2024-09-25' THEN a.from_address
    END) AS total_new_users
    FROM kaia.core.fact_transactions a
    JOIN kaia.core.dim_labels b ON a.to_address = b.address
    JOIN first_time_users f ON a.from_address = f.from_address
    WHERE a.tx_succeeded = 'TRUE'
    AND DATE >= '2024-09-25' -- Only after the Kaia Wave
    GROUP BY 1, 2
    )

    SELECT
    Date,
    -- Project_name,
    sector,
    total_txns,
    total_users,
    total_new_users
    FROM sector_activity
    ORDER BY total_txns DESC
    QueryRunArchived: QueryRun has been archived