Tobi_1necessary-violet
    Updated 2024-09-21
    WITH proposer_users AS (
    SELECT DISTINCT PROPOSER AS user
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    ),
    payer_users AS (
    SELECT DISTINCT PAYER AS user
    FROM flow.core.fact_transactions
    WHERE BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    ),
    authorizer_users AS (
    SELECT DISTINCT f.VALUE AS user
    FROM flow.core.fact_transactions,
    TABLE(FLATTEN(input => AUTHORIZERS)) f
    WHERE BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
    )
    -- Combine all distinct users and remove duplicates
    SELECT
    COUNT(DISTINCT user) AS total_unique_users
    FROM (
    SELECT user FROM proposer_users
    UNION
    SELECT user FROM payer_users
    UNION
    SELECT user FROM authorizer_users
    ) all_users;