Fact Event Logs

-- Find all ERC-20 Transfer events in last 24 hours
SELECT 
    block_timestamp,
    tx_hash,
    contract_address,
    topics[1] AS from_address_padded,
    topics[2] AS to_address_padded,
    data AS amount_hex,
    event_index
FROM crosschain.evm.fact_event_logs
WHERE topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
    AND ARRAY_SIZE(topics) = 3  -- ERC-20 has 3 topics
    AND block_timestamp >= CURRENT_DATE - 1
LIMIT 100;

-- Most active contracts by event count
SELECT 
    contract_address,
    COUNT(*) AS event_count,
    COUNT(DISTINCT tx_hash) AS unique_txs,
    COUNT(DISTINCT topic_0) AS unique_event_types,
    MIN(block_timestamp) AS first_seen,
    MAX(block_timestamp) AS last_seen
FROM crosschain.evm.fact_event_logs
WHERE block_timestamp >= CURRENT_DATE - 7
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50;

-- Event patterns within transactions
SELECT 
    tx_hash,
    COUNT(*) AS events_in_tx,
    COUNT(DISTINCT contract_address) AS contracts_touched,
    ARRAY_AGG(DISTINCT topic_0) AS event_signatures
FROM crosschain.evm.fact_event_logs
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY 2 DESC
LIMIT 20;