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)-- @TODO: use traces to track internal txns, first_appearance AS (SELECT MIN(block_time) AS first_block_time, to AS contractFROM bob.transactionsWHERE 1=1AND to 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') -- bob public launch was may 1st-- @TODO replace this ^^ with your own chain's launch dateGROUP BY 2), new_contracts AS (SELECT date_trunc('week', first_block_time) AS time, CAST(COUNT(contract) AS double) AS new_contractsFROM first_appearanceGROUP BY 1), total_data AS (SELECT date_trunc('week', block_time) AS time, CAST(COUNT(distinct to) AS double) AS total_contractsFROM bob.transactionsWHERE 1=1AND to 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_contracts-nu.new_contracts AS returning_contracts, nu.new_contracts, AVG(td.total_contracts) OVER(ORDER BY td.time ROWS BETWEEN 30 PRECEDING AND CURRENT ROW) AS contracts_moving_averageFROM total_data tdLEFT JOIN new_contracts nu ON td.time=nu.time