-- Query: MoM active host growth since Nov 2023
-- Result variable: monthly_host_growth

-- Monthly active hosts and MoM growth since Nov 2023
WITH monthly_active_hosts AS (
    SELECT
        DATE_TRUNC('month', tm.tool_timestamp)::DATE AS month,
        COUNT(DISTINCT c.host_id) AS active_hosts
    FROM hostagentspg.public.tool_messages AS tm
    INNER JOIN
        hostagentspg.public.conversations AS c
        ON tm.conversation_id = c.id
    WHERE tm.tool_timestamp >= '2023-11-01'
    GROUP BY DATE_TRUNC('month', tm.tool_timestamp)::DATE
),

growth_calc AS (
    SELECT
        month,
        active_hosts,
        LAG(active_hosts) OVER (ORDER BY month) AS prev_month_hosts,
        ROUND(
            (
                (
                    active_hosts - LAG(active_hosts) OVER (ORDER BY month)
                )::NUMERIC
                / NULLIF(LAG(active_hosts) OVER (ORDER BY month), 0) * 100
            ),
            2
        ) AS mom_growth_rate_pct
    FROM monthly_active_hosts
)

SELECT
    month,
    active_hosts,
    prev_month_hosts,
    mom_growth_rate_pct,
    active_hosts - prev_month_hosts AS net_change
FROM growth_calc
ORDER BY month