-- Query: 7-day rolling active hosts
-- Result variable: rolling_active_hosts

WITH active_hosts_daily AS (
    SELECT
        date,
        host_id,
        tool_message_count
    FROM daily_host_activity
    WHERE tool_message_count >= 1
        AND date >= '2025-01-01'
),

date_series AS (
    SELECT
        generate_series(
            min(date),
            max(date),
            INTERVAL '1 day'
        )::DATE AS date
    FROM active_hosts_daily
),

rolling_window AS (
    SELECT
        ds.date,
        count(DISTINCT ahd.host_id) AS active_hosts_7day_rolling
    FROM date_series AS ds
    LEFT JOIN active_hosts_daily AS ahd
        ON
            ahd.date >= ds.date - INTERVAL '6 days'
            AND ds.date >= ahd.date
    GROUP BY ds.date
)

SELECT
    date,
    active_hosts_7day_rolling
FROM rolling_window
ORDER BY date