Fact Transactions

SELECT 
    DATE_TRUNC('day', block_timestamp) AS day,
    blockchain,
    native_token,
    tx_type,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(tx_fee) AS total_fees_native,
    AVG(tx_fee) AS avg_fee_native,
    SUM(value) AS total_value_transferred
FROM crosschain.evm.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3, 4
ORDER BY 1 DESC, 2, 6 DESC;

SELECT 
    blockchain,
    native_token,
    tx_hash,
    block_timestamp,
    from_address,
    to_address,
    value,
    tx_fee,
    tx_succeeded
FROM crosschain.evm.fact_transactions
WHERE value > 0
    AND tx_succeeded
    AND block_timestamp >= CURRENT_DATE - 7
    AND blockchain = 'ethereum'
ORDER BY value DESC
LIMIT 100;

SELECT 
    blockchain,
    native_token,
    to_address,
    origin_function_signature,
    COUNT(*) AS interaction_count,
    COUNT(DISTINCT from_address) AS unique_users,
    SUM(tx_fee) AS total_fees_paid_native,
    AVG(tx_fee) AS avg_fee_native
FROM crosschain.evm.fact_transactions
WHERE to_address IN (SELECT address FROM crosschain.evm.dim_contracts)
    AND block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3, 4
ORDER BY 5 DESC
LIMIT 50;

SELECT 
    blockchain,
    native_token,
    DATE_TRUNC('hour', block_timestamp) AS hour,
    COUNT(*) AS transaction_count,
    AVG(tx_fee) AS avg_fee_native,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY tx_fee) AS median_fee_native,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY tx_fee) AS p95_fee_native,
    SUM(tx_fee) AS total_fees_native
FROM crosschain.evm.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 7
    AND tx_succeeded
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3 DESC;

SELECT 
    blockchain,
    native_token,
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS daily_transactions,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_recipients,
    SUM(value) AS total_value_transferred_native,
    SUM(tx_fee) AS total_fees_collected_native,
    AVG(value) AS avg_transaction_value_native
FROM crosschain.evm.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
    AND tx_succeeded
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3 DESC;

SELECT 
    blockchain,
    native_token,
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS failed_transactions,
    COUNT(DISTINCT from_address) AS unique_failed_senders,
    SUM(tx_fee) AS total_fees_lost_native,
    AVG(tx_fee) AS avg_fee_lost_native
FROM crosschain.evm.fact_transactions
WHERE NOT tx_succeeded
    AND block_timestamp >= CURRENT_DATE - 7
    AND blockchain = 'ethereum'
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3 DESC;