damidezNew user
    Updated 2024-10-03
    WITH FirstTransaction AS (
    SELECT
    decoded_log:sender AS Sender,
    MIN(Block_timestamp) AS First_Transaction_Date
    FROM bsc.core.fact_decoded_event_logs
    WHERE contract_address = '0x18b2a687610328590bc8f2e5fedde3b582a49cda'
    AND event_name IN ('BetBull', 'BetBear')
    AND block_timestamp >= '2021-08-26' -- Platform launch date
    GROUP BY Sender
    ),
    NewUsers AS (
    SELECT
    Sender,
    First_Transaction_Date
    FROM FirstTransaction
    WHERE First_Transaction_Date >= '2024-01-01'
    )

    SELECT
    DATE_TRUNC('week', First_Transaction_Date) AS Date,
    COUNT(DISTINCT Sender) AS Daily_New_Users,
    SUM(COUNT(DISTINCT Sender)) OVER (ORDER BY DATE_TRUNC('week', First_Transaction_Date)) AS Cumulative_Users
    FROM NewUsers
    GROUP BY DATE_TRUNC('week', First_Transaction_Date)
    ORDER BY Date DESC;

    QueryRunArchived: QueryRun has been archived