-- Query: Cumulative subscriptions by type over time
-- Result variable: subscription_history

WITH daily_subscriptions AS (
    SELECT
        created_at::DATE AS date,
        pricing_plan_name,
        COUNT(*) AS new_subscriptions
    FROM hostagentspg.public.subscriptions
    WHERE status = 'active'
    GROUP BY created_at::DATE, pricing_plan_name
),

date_series AS (
    SELECT
        GENERATE_SERIES(
            (SELECT MIN(date) FROM daily_subscriptions),
            (SELECT MAX(date) FROM daily_subscriptions),
            INTERVAL '1 day'
        )::DATE AS date
),

all_plans AS (
    SELECT DISTINCT pricing_plan_name
    FROM hostagentspg.public.subscriptions
    WHERE status = 'active'
),

date_plan_matrix AS (
    SELECT
        ds.date,
        ap.pricing_plan_name,
        COALESCE(dsub.new_subscriptions, 0) AS new_subscriptions
    FROM date_series AS ds
    CROSS JOIN all_plans AS ap
    LEFT JOIN daily_subscriptions AS dsub
        ON
            ds.date = dsub.date
            AND ap.pricing_plan_name = dsub.pricing_plan_name
)

SELECT
    date,
    pricing_plan_name,
    SUM(new_subscriptions) OVER (
        PARTITION BY pricing_plan_name
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_subscriptions
FROM date_plan_matrix
ORDER BY date, pricing_plan_name