Ez Core Metrics Hourly

-- 24-hour network health summary
WITH hourly_stats AS (
    SELECT 
        block_timestamp_hour,
        transaction_count,
        transaction_count_success,
        transaction_count_failed,
        ROUND(100.0 * transaction_count_success / NULLIF(transaction_count, 0), 2) AS success_rate,
        total_fees_native,
        total_fees_usd,
        unique_from_count AS active_users,
        block_count,
        ROUND(transaction_count::FLOAT / NULLIF(block_count, 0), 2) AS avg_tx_per_block
    FROM <blockchain_name>.stats.ez_core_metrics_hourly
    WHERE block_timestamp_hour >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
)
SELECT 
    COUNT(*) AS hours_of_data,
    SUM(transaction_count) AS total_transactions,
    AVG(success_rate) AS avg_success_rate,
    SUM(total_fees_usd) AS total_fees_usd_24h,
    SUM(active_users) AS unique_active_addresses,
    AVG(avg_tx_per_block) AS avg_tx_per_block_24h,
    MAX(transaction_count) AS peak_hourly_transactions,
    MIN(block_count) AS min_blocks_per_hour,
    MAX(block_count) AS max_blocks_per_hour
FROM hourly_stats;

-- Daily average gas fees with volatility
SELECT 
    DATE(block_timestamp_hour) AS date,
    AVG(total_fees_native / NULLIF(transaction_count, 0)) AS avg_fee_per_tx_native,
    AVG(total_fees_usd / NULLIF(transaction_count, 0)) AS avg_fee_per_tx_usd,
    STDDEV(total_fees_usd / NULLIF(transaction_count, 0)) AS fee_volatility_usd,
    MIN(total_fees_usd / NULLIF(transaction_count, 0)) AS min_fee_per_tx_usd,
    MAX(total_fees_usd / NULLIF(transaction_count, 0)) AS max_fee_per_tx_usd,
    SUM(transaction_count) AS daily_transactions,
    SUM(total_fees_usd) AS daily_fees_usd
FROM <blockchain_name>.stats.ez_core_metrics_hourly
WHERE block_timestamp_hour >= CURRENT_DATE - 30
    AND transaction_count > 0
GROUP BY 1
ORDER BY 1 DESC;

-- Hourly activity patterns (UTC)
SELECT 
    EXTRACT(HOUR FROM block_timestamp_hour) AS hour_utc,
    AVG(transaction_count) AS avg_transactions,
    AVG(unique_from_count) AS avg_active_users,
    AVG(unique_to_count) AS avg_unique_recipients,
    AVG(transaction_count::FLOAT / NULLIF(unique_from_count, 0)) AS avg_tx_per_user,
    STDDEV(transaction_count) AS transaction_volatility
FROM <blockchain_name>.stats.ez_core_metrics_hourly
WHERE block_timestamp_hour >= CURRENT_DATE - 7
GROUP BY 1
ORDER BY 1;

-- Identify high congestion periods
WITH congestion_metrics AS (
    SELECT 
        block_timestamp_hour,
        transaction_count,
        block_count,
        transaction_count::FLOAT / NULLIF(block_count, 0) AS tx_per_block,
        total_fees_usd / NULLIF(transaction_count, 0) AS avg_fee_usd,
        transaction_count_failed::FLOAT / NULLIF(transaction_count, 0) * 100 AS failure_rate
    FROM <blockchain_name>.stats.ez_core_metrics_hourly
    WHERE block_timestamp_hour >= CURRENT_DATE - 7
),
percentiles AS (
    SELECT 
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY tx_per_block) AS p90_tx_per_block,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY avg_fee_usd) AS p90_fee,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY failure_rate) AS p90_failure_rate
    FROM congestion_metrics
)
SELECT 
    c.block_timestamp_hour,
    c.tx_per_block,
    c.avg_fee_usd,
    c.failure_rate,
    CASE 
        WHEN c.tx_per_block > p.p90_tx_per_block 
             AND c.avg_fee_usd > p.p90_fee THEN 'High Congestion'
        WHEN c.failure_rate > p.p90_failure_rate THEN 'Network Issues'
        ELSE 'Normal'
    END AS network_status
FROM congestion_metrics c
CROSS JOIN percentiles p
WHERE c.tx_per_block > p.p90_tx_per_block 
   OR c.avg_fee_usd > p.p90_fee
   OR c.failure_rate > p.p90_failure_rate
ORDER BY c.block_timestamp_hour DESC;

-- Week-over-week growth analysis
WITH weekly_stats AS (
    SELECT 
        DATE_TRUNC('week', block_timestamp_hour) AS week,
        SUM(transaction_count) AS weekly_transactions,
        COUNT(DISTINCT unique_from_count) AS unique_weekly_users,
        SUM(total_fees_usd) AS weekly_fees_usd,
        AVG(transaction_count_success::FLOAT / NULLIF(transaction_count, 0)) AS avg_success_rate
    FROM <blockchain_name>.stats.ez_core_metrics_hourly
    WHERE block_timestamp_hour >= CURRENT_DATE - 60
    GROUP BY 1
)
SELECT 
    week,
    weekly_transactions,
    LAG(weekly_transactions) OVER (ORDER BY week) AS prev_week_transactions,
    ROUND(100.0 * (weekly_transactions - LAG(weekly_transactions) OVER (ORDER BY week)) / 
          NULLIF(LAG(weekly_transactions) OVER (ORDER BY week), 0), 2) AS tx_growth_pct,
    unique_weekly_users,
    ROUND(100.0 * (unique_weekly_users - LAG(unique_weekly_users) OVER (ORDER BY week)) / 
          NULLIF(LAG(unique_weekly_users) OVER (ORDER BY week), 0), 2) AS user_growth_pct,
    weekly_fees_usd,
    avg_success_rate
FROM weekly_stats
ORDER BY week DESC;