flipside

    Grouping users
    Afonso_DiazGrouping users
    Updated 2025-02-24
    WITH main AS (
    SELECT
    tx_hash,
    block_timestamp,
    from_address AS user
    FROM kaia.core.fact_transactions
    ),
    user_tx_count AS (
    SELECT
    user,
    COUNT(tx_hash) AS tx_count
    FROM main
    GROUP BY 1
    ),
    categorized_users AS (
    SELECT
    CASE
    WHEN tx_count = 1 THEN '1 Tx'
    WHEN tx_count BETWEEN 2 AND 5 THEN '2-5 Tx'
    WHEN tx_count BETWEEN 6 AND 10 THEN '6-10 Tx'
    WHEN tx_count BETWEEN 11 AND 20 THEN '11-20 Tx'
    ELSE '21+ Tx'
    END AS tx_category,
    COUNT(DISTINCT user) AS user_count
    FROM user_tx_count
    GROUP BY 1
    )
    SELECT
    tx_category,
    user_count
    FROM categorized_users

    QueryRunArchived: QueryRun has been archived