flipside

    electric-ivory
    datavortexelectric-ivory
    Updated 9 days ago
    WITH

    all_users_before_30d AS (
    SELECT DISTINCT
    origin_from_address AS user,
    platform
    FROM
    kaia.defi.ez_dex_swaps
    WHERE
    block_timestamp < CURRENT_TIMESTAMP - INTERVAL '30 days'
    ),


    daily_user_activity AS (
    SELECT
    DATE(block_timestamp) AS day,
    origin_from_address AS user,
    platform
    FROM
    kaia.defi.ez_dex_swaps
    WHERE
    block_timestamp > CURRENT_TIMESTAMP - INTERVAL '30 days'
    ),


    user_classification AS (
    SELECT
    d.day,
    d.user,
    d.platform,
    CASE
    WHEN b.user IS NULL THEN 'New User'
    ELSE 'Returning User'
    END AS user_type
    FROM
    daily_user_activity d
    Last run: 9 days ago
    Platform
    Date
    New Users
    Returning Users
    Total Users
    1
    		capybara2025-04-20 00:00:00.000147447594
    2
    		capybara2025-04-21 00:00:00.0002199821201
    3
    		capybara2025-04-22 00:00:00.00033119902321
    4
    		capybara2025-04-23 00:00:00.00070112091910
    5
    		capybara2025-04-24 00:00:00.00032299894218
    6
    		capybara2025-04-25 00:00:00.000139212202612
    7
    		capybara2025-04-26 00:00:00.000224754978
    8
    		capybara2025-04-27 00:00:00.0002638951158
    9
    		capybara2025-04-28 00:00:00.00035013611711
    10
    		capybara2025-04-29 00:00:00.00028111631444
    11
    		capybara2025-04-30 00:00:00.00019912151414
    12
    		capybara2025-05-01 00:00:00.00037410601434
    13
    		capybara2025-05-02 00:00:00.00032212611583
    14
    		capybara2025-05-03 00:00:00.0002178021019
    15
    		capybara2025-05-04 00:00:00.00063512251860
    16
    		capybara2025-05-05 00:00:00.00040212201622
    17
    		capybara2025-05-06 00:00:00.00049413211815
    18
    		capybara2025-05-07 00:00:00.000182319233746
    19
    		capybara2025-05-08 00:00:00.00045928793338
    20
    		capybara2025-05-09 00:00:00.00060839354543
    ...
    217
    11KB
    3s