Fact Traces

-- Analyze internal ETH transfers
SELECT 
    block_timestamp,
    tx_hash,
    trace_index,
    type,
    from_address,
    to_address,
    value,
    gas_used,
    trace_succeeded
FROM crosschain.evm.fact_traces
WHERE value > 0
    AND type = 'CALL'
    AND trace_succeeded
    AND block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
ORDER BY value DESC
LIMIT 100;

-- Find failed internal transactions with reasons
SELECT 
    tx_hash,
    trace_index,
    from_address,
    to_address,
    type,
    error_reason,
    revert_reason,
    gas,
    gas_used,
    blockchain
FROM crosschain.evm.fact_traces
WHERE NOT trace_succeeded
    AND block_timestamp >= CURRENT_DATE - 1
    AND error_reason IS NOT NULL
LIMIT 50;

-- Trace execution depth analysis
SELECT 
    tx_hash,
    MAX(ARRAY_SIZE(trace_address)) AS max_depth,
    COUNT(*) AS total_traces,
    SUM(CASE WHEN trace_succeeded THEN 0 ELSE 1 END) AS failed_traces,
    SUM(value) AS total_value_transferred
FROM crosschain.evm.fact_traces
WHERE block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
GROUP BY 1
HAVING MAX(ARRAY_SIZE(trace_address)) > 3
ORDER BY 2 DESC;

-- Contract interaction patterns
WITH contract_calls AS (
    SELECT 
        from_address AS caller,
        to_address AS callee,
        COUNT(*) AS call_count,
        SUM(value) AS total_value
    FROM crosschain.evm.fact_traces
    WHERE type IN ('CALL', 'DELEGATECALL')
        AND block_timestamp >= CURRENT_DATE - 7
        AND blockchain = 'ethereum'
    GROUP BY 1, 2
)
SELECT * FROM contract_calls
WHERE call_count > 100
ORDER BY call_count DESC;