SELECT

block_number,

DATE_TRUNC('hour', block_timestamp) AS creation_hour,

tx_hash,

from_address AS contract_creator,

to_address AS new_contract_address,

input AS contract_creation_code,

INSERTED_TIMESTAMP,

block_timestamp,

LAG(block_timestamp) OVER (ORDER BY block_timestamp) AS previous_block_timestamp

FROM

blast.core.fact_traces

WHERE

type ILIKE 'create%' AND

to_address IS NOT NULL AND

input <> '0x' AND

tx_status = 'SUCCESS' AND

trace_status = 'SUCCESS'

),

creation_stats AS (

SELECT

creation_hour,

COUNT(DISTINCT tx_hash) AS contracts_deployed,

COUNT(DISTINCT contract_creator) AS unique_creators,

AVG(CASE

WHEN previous_block_timestamp IS NOT NULL THEN

DATEDIFF('second', previous_block_timestamp, block_timestamp)

ELSE

0

END) AS avg_block_time_seconds

FROM

refined_contract_creations

GROUP BY

creation_hour

),