Query editor
WITH smart_contracts_list AS (select distinct addressfrom bob.traceswhere "type" like 'create%'and address is not nulland successand code != 0x and code is not null), first_appearance AS (SELECT MIN(block_time) AS first_block_time, "from" AS userFROM bob.transactionsWHERE 1=1AND "from" NOT IN (SELECT address FROM smart_contracts_list)AND block_time < date_trunc('week', (SELECT MAX(block_time) FROM bob.transactions))and block_time >= date('2024-05-01')-- @TODO replace this ^^ with your own chain's launch dateGROUP BY 2), new_users AS (SELECT date_trunc('week', first_block_time) AS time, CAST(COUNT(user) AS double) AS new_usersFROM first_appearanceGROUP BY 1), total_data AS (SELECT date_trunc('week', block_time) AS time, CAST(COUNT(distinct "from") AS double) AS total_usersFROM bob.transactionsWHERE 1=1AND "from" NOT IN (SELECT address FROM smart_contracts_list)AND block_time < date_trunc('week', (SELECT MAX(block_time) FROM bob.transactions))GROUP BY 1)SELECT td.time, td.total_users-nu.new_users AS returning_users, nu.new_users, AVG(td.total_users) OVER(ORDER BY td.time ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS users_moving_averageFROM total_data tdLEFT JOIN new_users nu ON td.time=nu.time