Fact Transactions

-- Daily transaction statistics by type
SELECT 
    DATE_TRUNC('day', block_timestamp) AS day,
    tx_type,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(tx_fee) AS total_fees_native,
    AVG(gas_used) AS avg_gas_used,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY gas_price) AS median_gas_price
FROM <blockchain_name>.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- High-value native token transfers
SELECT 
    tx_hash,
    block_timestamp,
    from_address,
    to_address,
    value,
    tx_fee,
    gas_used * gas_price / 1e9 AS gas_cost_gwei
FROM <blockchain_name>.core.fact_transactions
WHERE value > 0
    AND tx_succeeded
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY value DESC
LIMIT 100;

-- Smart contract interaction patterns
SELECT 
    to_address,
    origin_function_signature,
    COUNT(*) AS interaction_count,
    COUNT(DISTINCT from_address) AS unique_users,
    SUM(tx_fee) AS total_fees_paid
FROM <blockchain_name>.core.fact_transactions
WHERE to_address IN (SELECT address FROM dim_contracts)
    AND block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 50;