Full Time Series Dashboard

<!-- ❌ template — adapt CTE aliases to your data --> — Multi-Signal Composite Query

This lesson combines all prior techniques into a single operational dashboard CTE that gives an at-a-glance view of the current state, trend, anomaly status, and forecast for every monitored server — the kind of query a senior DBA or SRE would write for a real-time operations page.

-- ❌ template
-- Operational server health dashboard: current state + trend + anomaly + forecast
WITH
-- 1. Last 24h hourly averages per host
hourly_avg AS (
    SELECT
        host,
        region,
        DATE_TRUNC('hour', recorded_at)                AS hour_bucket,
        AVG(cpu_pct)                                   AS avg_cpu,
        AVG(mem_pct)                                   AS avg_mem,
        AVG(net_mbps)                                  AS avg_net,
        SUM(error_count)                               AS errors,
        COUNT(*)                                       AS readings
    FROM ts_server_metrics
    WHERE recorded_at >= NOW() - INTERVAL '24 hours'
    GROUP BY host, region, DATE_TRUNC('hour', recorded_at)
),
-- 2. Current hour snapshot (latest bucket)
current_hour AS (
    SELECT DISTINCT ON (host)
        host, region, hour_bucket,
        avg_cpu, avg_mem, avg_net, errors, readings
    FROM hourly_avg
    ORDER BY host, hour_bucket DESC
),
-- 3. 24-hour trend using linear regression
trend_24h AS (
    SELECT
        host,
        REGR_SLOPE(avg_cpu, EXTRACT(EPOCH FROM hour_bucket))   AS cpu_slope,
        REGR_R2(avg_cpu, EXTRACT(EPOCH FROM hour_bucket))      AS cpu_r2,
        STDDEV_SAMP(avg_cpu)                                   AS cpu_std,
        AVG(avg_cpu)                                           AS cpu_mean_24h,
        MAX(avg_cpu)                                           AS cpu_max_24h,
        PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY avg_cpu) AS cpu_p95
    FROM hourly_avg
    GROUP BY host
),
-- 4. Rolling 7-day baseline (for anomaly comparison)
baseline_7d AS (
    SELECT
        host,
        AVG(cpu_pct)        AS baseline_cpu,
        STDDEV_SAMP(cpu_pct) AS baseline_std
    FROM ts_server_metrics
    WHERE recorded_at BETWEEN NOW() - INTERVAL '7 days'
                          AND NOW() - INTERVAL '24 hours'
    GROUP BY host
),
-- 5. Gap detection: hours with < 50 readings in the last 24h
gaps_24h AS (
    SELECT
        host,
        COUNT(*) FILTER (WHERE readings < 50)          AS sparse_hours,
        COUNT(*) FILTER (WHERE readings = 0)           AS missing_hours
    FROM (
        SELECT h.host, COALESCE(a.readings, 0) AS readings
        FROM (SELECT DISTINCT host FROM hourly_avg) h
        CROSS JOIN generate_series(
            DATE_TRUNC('hour', NOW() - INTERVAL '24 hours'),
            DATE_TRUNC('hour', NOW()) - INTERVAL '1 hour',
            INTERVAL '1 hour'
        ) spine(hr)
        LEFT JOIN hourly_avg a ON a.host = h.host AND a.hour_bucket = spine.hr
    ) coverage
    GROUP BY host
)
-- Final: combine all signals
SELECT
    ch.host,
    ch.region,

    -- Current state
    ROUND(ch.avg_cpu::numeric, 1)                      AS current_cpu_pct,
    ROUND(ch.avg_mem::numeric, 1)                      AS current_mem_pct,
    ROUND(ch.avg_net::numeric, 1)                      AS current_net_mbps,
    ch.errors                                          AS errors_this_hour,

    -- 24h statistics
    ROUND(t.cpu_mean_24h::numeric, 1)                  AS cpu_avg_24h,
    ROUND(t.cpu_p95::numeric, 1)                       AS cpu_p95_24h,
    ROUND(t.cpu_max_24h::numeric, 1)                   AS cpu_max_24h,

    -- Trend (positive = rising, negative = falling)
    CASE
        WHEN t.cpu_slope > 0.005  THEN 'Rising fast'
        WHEN t.cpu_slope > 0.001  THEN 'Rising'
        WHEN t.cpu_slope < -0.005 THEN 'Falling fast'
        WHEN t.cpu_slope < -0.001 THEN 'Falling'
        ELSE                           'Stable'
    END                                                AS cpu_trend,

    -- Z-score vs 7-day baseline
    ROUND(((ch.avg_cpu - b.baseline_cpu)
           / NULLIF(b.baseline_std, 0))::numeric, 2)   AS cpu_z_vs_baseline,

    -- Anomaly flag
    CASE
        WHEN ABS((ch.avg_cpu - b.baseline_cpu)
                 / NULLIF(b.baseline_std, 0)) > 3      THEN 'CRITICAL'
        WHEN ABS((ch.avg_cpu - b.baseline_cpu)
                 / NULLIF(b.baseline_std, 0)) > 2      THEN 'WARNING'
        WHEN ch.errors > 0                             THEN 'ERRORS'
        WHEN g.sparse_hours > 2                        THEN 'DATA GAPS'
        ELSE                                                'OK'
    END                                                AS status,

    -- Coverage
    g.sparse_hours,
    g.missing_hours,

    -- Model confidence
    ROUND(t.cpu_r2::numeric, 3)                        AS trend_r2
FROM current_hour ch
JOIN trend_24h t     ON t.host = ch.host
JOIN baseline_7d b   ON b.host = ch.host
JOIN gaps_24h g      ON g.host = ch.host
ORDER BY
    CASE WHEN status = 'CRITICAL' THEN 0
         WHEN status = 'WARNING'  THEN 1
         WHEN status = 'ERRORS'   THEN 2
         WHEN status = 'DATA GAPS' THEN 3
         ELSE 4 END,
    cpu_z_vs_baseline DESC;