flipside

    Daily Active Wallets
    feyikemiDaily Active Wallets
    Updated 2025-03-26
    WITH daily_active AS (
    SELECT
    DATE_TRUNC('day', BLOCK_TIMESTAMP) AS Date,
    COUNT(DISTINCT FROM_ADDRESS) AS active_wallets
    FROM ronin.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = '0xf988f63bf26c3ed3fbf39922149e3e7b1e5c27cb'
    AND Date >= '2025-02-22'
    GROUP BY date
    ),

    wallet_first_seen AS (
    SELECT
    FROM_ADDRESS,
    MIN(DATE_TRUNC('day', BLOCK_TIMESTAMP)) AS first_seen_date
    FROM ronin.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = '0xf988f63bf26c3ed3fbf39922149e3e7b1e5c27cb'
    GROUP BY FROM_ADDRESS
    ),

    cumulative_wallets AS (
    SELECT
    first_seen_date,
    COUNT(*) AS new_wallets
    FROM wallet_first_seen
    GROUP BY first_seen_date
    )

    SELECT
    Date,
    active_wallets,
    SUM(COALESCE(c.new_wallets, 0)) OVER (ORDER BY d.date) AS cumulative_wallets
    FROM daily_active d
    LEFT JOIN cumulative_wallets c
    ON d.date = c.first_seen_date
    ORDER BY d.date DESC
    QueryRunArchived: QueryRun has been archived