crypto_gostessential-apricot
    Updated 2024-10-24
    WITH user_first_activity AS (
    SELECT
    signers[0] AS user_address,
    MIN(block_timestamp) AS first_activity_date
    FROM solana.core.fact_events
    WHERE program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'
    GROUP BY signers[0]
    ),

    user_activity AS (
    SELECT
    signers[0] AS user_address,
    date_trunc('d', block_timestamp) AS date
    FROM solana.core.fact_events
    WHERE program_id = 'RainEraPU5yDoJmTrHdYynK9739GkEfDsE4ffqce2BR'
    AND block_timestamp::DATE >= DATEADD(DAY, -60, CURRENT_DATE())
    ),

    daily_users AS (
    SELECT
    ua.date,
    COUNT(DISTINCT CASE
    WHEN ua.date = u.first_activity_date THEN ua.user_address
    END) AS new_users,
    COUNT(DISTINCT CASE
    WHEN ua.date != u.first_activity_date THEN ua.user_address
    END) AS recurring_users,
    COUNT(DISTINCT ua.user_address) AS total_daily_users
    FROM user_activity ua
    LEFT JOIN user_first_activity u
    ON ua.user_address = u.user_address
    GROUP BY ua.date
    )

    SELECT
    date,
    QueryRunArchived: QueryRun has been archived