rajsOsmosis
    Updated 2022-11-01
    WITH days AS (
    SELECT DATE_TRUNC('DAY', block_timestamp) as day, tx_from as user_address
    FROM osmosis.core.fact_transactions
    WHERE block_timestamp BETWEEN current_date - 367 and current_date - 2
    GROUP BY day, tx_from
    ),

    week AS (
    SELECT DATE_TRUNC('WEEK', day) as week, user_address, COUNT(*) as num_days
    FROM days
    GROUP BY week, user_address
    HAVING num_days >= {{num_days}}
    ),

    active_users AS (
    SELECT week, COUNT(*) as num_active_users
    FROM week
    GROUP BY week
    )

    SELECT *
    FROM active_users
    order by 1

    Run a query to Download Data