Mrftipreferred-turquoise copy
    Updated 5 hours ago
    -- forked from preferred-turquoise @ https://flipsidecrypto.xyz/studio/queries/e893dfbc-ecbe-45a3-9aec-8820266e9080

    WITH first_appearance AS (
    SELECT
    from_address,
    MIN(DATE_TRUNC('day', block_timestamp)) AS first_appearance_date
    FROM swell.core.fact_transactions
    GROUP BY 1
    ),
    daily_new_users AS (
    SELECT
    DATE_TRUNC('day', t.block_timestamp) AS date,
    COUNT(DISTINCT t.from_address) AS daily_new_addresses
    FROM swell.core.fact_transactions t
    JOIN first_appearance fa ON t.from_address = fa.from_address
    WHERE DATE_TRUNC('day', t.block_timestamp) = fa.first_appearance_date
    GROUP BY 1
    ),
    cumulative_users AS (
    SELECT
    date,
    daily_new_addresses,
    SUM(daily_new_addresses) OVER (ORDER BY date) AS cumulative_addresses
    FROM daily_new_users
    )

    SELECT
    date,
    daily_new_addresses AS "Daily New Addresses",
    cumulative_addresses AS "Cumulative Unique Addresses"
    FROM cumulative_users
    ORDER BY 1


    Last run: about 5 hours agoAuto-refreshes every 12 hours
    DATE
    Daily New Addresses
    Cumulative Unique Addresses
    1
    2024-11-27 00:00:00.00011
    2
    2024-11-28 00:00:00.00023
    3
    2024-11-29 00:00:00.00014
    4
    2024-12-01 00:00:00.00015
    5
    2024-12-02 00:00:00.000611
    6
    2024-12-03 00:00:00.000208219
    7
    2024-12-04 00:00:00.0008227
    8
    2024-12-05 00:00:00.0008235
    9
    2024-12-06 00:00:00.00013248
    10
    2024-12-07 00:00:00.0002250
    11
    2024-12-08 00:00:00.0006256
    12
    2024-12-09 00:00:00.00013269
    13
    2024-12-10 00:00:00.00010279
    14
    2024-12-11 00:00:00.00019298
    15
    2024-12-12 00:00:00.0009307
    16
    2024-12-13 00:00:00.0009316
    17
    2024-12-14 00:00:00.0009325
    18
    2024-12-15 00:00:00.0006331
    19
    2024-12-16 00:00:00.00011342
    20
    2024-12-17 00:00:00.00016358
    ...
    109
    4KB
    3s