Updated 2025-01-26
    WITH users AS (
    SELECT
    FROM_ADDRESS as user,
    COUNT(tx_hash) AS num_interactions
    FROM
    flow.core_evm.fact_transactions
    where STATUS='SUCCESS'
    GROUP BY
    1
    ),
    interaction AS (
    SELECT
    user,
    CASE
    WHEN num_interactions = 1 THEN '1 tx'
    WHEN num_interactions <= 10 THEN '1-10 tx'
    WHEN num_interactions <= 50 THEN '11-50 tx'
    WHEN num_interactions <= 100 THEN '51-100 tx'
    WHEN num_interactions <= 500 THEN '101-500 tx'
    WHEN num_interactions <= 1000 THEN '501-1000 tx'
    ELSE '1001+ tx'
    END AS interaction_status
    FROM
    users
    )
    SELECT
    interaction_status,
    COUNT(DISTINCT user) AS active_users
    FROM
    interaction
    GROUP BY
    interaction_status
    ORDER BY
    active_users DESC;


    Last run: 20 days ago
    INTERACTION_STATUS
    ACTIVE_USERS
    1
    1 tx1765
    2
    1-10 tx1405
    3
    11-50 tx780
    4
    51-100 tx135
    5
    101-500 tx126
    6
    1001+ tx39
    7
    501-1000 tx15
    7
    121B
    2s