-- Query: Combined 7-day rolling & 1-day active hosts
-- Result variable: combined_active_hosts

WITH active_hosts_1day AS (
    SELECT
        date,
        COUNT(DISTINCT host_id) AS active_hosts_1day
    FROM daily_host_activity
    WHERE tool_message_count >= 1
    GROUP BY date
),
active_hosts_daily AS (
    SELECT 
        date,
        host_id,
        tool_message_count
    FROM daily_host_activity
    WHERE tool_message_count >= 1
),
date_series AS (
    SELECT 
        generate_series(
            MIN(date),
            MAX(date),
            INTERVAL '1 day'
        )::DATE as date
    FROM active_hosts_daily
),
rolling_30day AS (
    SELECT 
        ds.date,
        COUNT(DISTINCT ahd.host_id) as active_hosts_30day_rolling
    FROM date_series ds
    LEFT JOIN active_hosts_daily ahd 
        ON ahd.date >= ds.date - INTERVAL '29 days' 
        AND ahd.date <= ds.date
    GROUP BY ds.date
)

SELECT
    r.date,
    r.active_hosts_7day_rolling,
    a.active_hosts_1day,
    r30.active_hosts_30day_rolling
FROM rolling_active_hosts AS r
LEFT JOIN active_hosts_1day AS a ON r.date = a.date
LEFT JOIN rolling_30day AS r30 ON r.date = r30.date
ORDER BY r.date