Query editor
WITH smart_contracts_list AS (select distinct addressfrom unichain.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 unichain.transactionsWHERE 1=1AND "from" NOT IN (SELECT address FROM smart_contracts_list)AND block_time < date_trunc('day', (SELECT MAX(block_time) FROM unichain.transactions))and block_time >= date('2025-02-01')GROUP BY 2), new_users AS (SELECT date_trunc('day', first_block_time) AS time, CAST(COUNT(user) AS double) AS new_usersFROM first_appearanceGROUP BY 1), total_data AS (SELECT date_trunc('day', block_time) AS time, CAST(COUNT(distinct "from") AS double) AS total_usersFROM unichain.transactionsWHERE 1=1AND "from" NOT IN (SELECT address FROM smart_contracts_list)AND block_time < date_trunc('day', (SELECT MAX(block_time) FROM unichain.transactions))AND block_time >= date('2025-02-01')GROUP BY 1)SELECTtd.time,td.total_users-COALESCE(nu.new_users, 0) AS returning_users,COALESCE(nu.new_users, 0) as new_users,AVG(td.total_users) OVER(ORDER BY td.time ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS users_moving_averageFROM total_data tdLEFT JOIN new_users nu ON td.time=nu.timeORDER BY td.time;