WITH traces AS (

SELECT *

, CASE WHEN bytelength >= {{min_code_length}} THEN 1 ELSE 0 END AS is_min_length

, CASE WHEN code_rn = 1 THEN 1 ELSE 0 END AS is_first_deployment

, CASE WHEN EXISTS (SELECT 1 FROM base.goerli.fact_traces s

WHERE s.to_address = contract_address

AND s.block_number >= a.block_number

AND type NOT IN ('CREATE','SUICIDE')

) THEN 1 ELSE 0 END AS is_contract_used

, CASE WHEN EXISTS (SELECT 1 FROM base.goerli.fact_traces s

WHERE s.to_address = contract_address

AND s.block_number >= a.block_number

AND type = 'SUICIDE'

) THEN 0 ELSE 1 END AS is_not_destructed

FROM (

SELECT r.tx_hash, r.block_timestamp, r.block_number, t.from_address

, data['to'] as contract_address

, ROW_NUMBER() OVER (PARTITION BY output ORDER BY r.block_number ASC) AS code_rn,

(length(output)-2)/2 as bytelength

FROM base.goerli.fact_traces r

INNER JOIN base.goerli.fact_transactions t

ON r.block_number = t.block_number

AND r.BLOCK_TIMESTAMP = t.BLOCK_TIMESTAMP

AND r.tx_hash = t.tx_hash

AND r.FROM_ADDRESS = t.FROM_ADDRESS --eoa deploy

where type = 'CREATE'

and tx_status = 'SUCCESS'

-- AND t.block_timestamp > DATEADD(Day ,-1, current_timestamp)

) a