traces_with_status.sql

WITH traces_with_status AS (
    -- Find all nested traces of failed traces
    WITH nested_failed_traces AS (
        SELECT distinct child.transaction_hash, child.trace_address
        FROM `bigquery-public-data.ethereum_blockchain.traces` parent
        JOIN `bigquery-public-data.ethereum_blockchain.traces` child
        ON (parent.trace_address IS NULL OR starts_with(child.trace_address, concat(parent.trace_address, ',')))
        AND child.transaction_hash = parent.transaction_hash
        where parent.trace_type IN ('call', 'create')
        AND parent.error IS NOT NULL
    )
    SELECT traces.*, if((traces.error IS NOT NULL or nested_failed_traces.trace_address IS NOT NULL), 0, 1) AS status
    FROM `bigquery-public-data.ethereum_blockchain.traces` AS traces
    LEFT JOIN nested_failed_traces ON nested_failed_traces.transaction_hash = traces.transaction_hash
    AND nested_failed_traces.trace_address = traces.trace_address
)
select * from traces_with_status