gigiokobavertical-indigo
    Updated 2025-02-09
    SELECT
    DATE_TRUNC('day', log_data.event_time) AS activity_period,
    CASE
    WHEN log_data.rank = 1 THEN 'First-Time Users'
    ELSE 'Repeat Users'
    END AS user_category,
    COUNT(DISTINCT log_data.user_address) AS user_count
    FROM (
    SELECT
    origin_from_address AS user_address,
    block_timestamp AS event_time,
    ROW_NUMBER() OVER (PARTITION BY origin_from_address ORDER BY block_timestamp ASC) AS rank
    FROM avalanche.core.ez_decoded_event_logs
    WHERE contract_address = '0xaaab9d12a30504559b0c5a9a5977fee4a6081c6b'
    ) AS log_data
    GROUP BY activity_period, user_category
    ORDER BY activity_period DESC;
    Last run: 14 days ago
    ACTIVITY_PERIOD
    USER_CATEGORY
    USER_COUNT
    1
    2025-02-09 00:00:00.000Repeat Users150
    2
    2025-02-09 00:00:00.000First-Time Users12
    3
    2025-02-08 00:00:00.000First-Time Users37
    4
    2025-02-08 00:00:00.000Repeat Users292
    5
    2025-02-07 00:00:00.000Repeat Users360
    6
    2025-02-07 00:00:00.000First-Time Users34
    7
    2025-02-06 00:00:00.000First-Time Users39
    8
    2025-02-06 00:00:00.000Repeat Users389
    9
    2025-02-05 00:00:00.000First-Time Users42
    10
    2025-02-05 00:00:00.000Repeat Users375
    11
    2025-02-04 00:00:00.000Repeat Users436
    12
    2025-02-04 00:00:00.000First-Time Users77
    13
    2025-02-03 00:00:00.000Repeat Users567
    14
    2025-02-03 00:00:00.000First-Time Users126
    15
    2025-02-02 00:00:00.000Repeat Users446
    16
    2025-02-02 00:00:00.000First-Time Users88
    17
    2025-02-01 00:00:00.000Repeat Users424
    18
    2025-02-01 00:00:00.000First-Time Users76
    19
    2025-01-31 00:00:00.000First-Time Users54
    20
    2025-01-31 00:00:00.000Repeat Users407
    ...
    859
    40KB
    8s