Compute monthly new subscribers and YoY deltas

Write a single SQL query that returns monthly counts of new subscribers starting at 2019-06, plus year-over-year (same-month prior year) comparisons. Treat a "new subscriber" as any company with a non-null subscription_date whose first (and only) subscription_date falls in that month. Include months with zero new subscribers. Return columns: month_start (DATE, first day of month), new_subscribers (INT), prior_year_new_subscribers (INT), yoy_abs_delta (INT), yoy_pct_delta (DECIMAL with 2 decimals; NULL if prior year is 0), and a flag is_partial_month (BOOLEAN) indicating whether the month is incomplete given the data watermark. The query must generate a proper month spine and perform a 12-month lag join, not rely on existing full-month rows. Assume the dataset may grow; do not hardcode end dates. Use subscription_date in UTC and explicitly truncate to month boundaries. Handle division-by-zero safely and ensure months with no data still appear with zeros. Schema to use and small sample data:

company
+------------+-------------+-------------------+------------------+
| company_id | signup_date | subscription_date | termination_date |
+------------+-------------+-------------------+------------------+
| 1 | 2019-05-20 | 2019-06-02 | 2020-01-10 |
| 2 | 2019-06-15 | 2019-06-20 | NULL |
| 3 | 2019-07-01 | 2019-07-05 | 2019-12-31 |
| 4 | 2020-06-10 | 2020-06-11 | NULL |
| 5 | 2020-06-30 | 2020-07-02 | NULL |
| 6 | 2020-07-15 | NULL | NULL |
+------------+-------------+-------------------+------------------+

Notes/constraints:

• Assume one row per company_id.
• Use the earliest available subscription_date per company_id (here it is unique already).
• Define data_watermark as the max(subscription_date) present; a month is partial if its last day > data_watermark.
• Start the spine at 2019-06-01 and end at the last month containing any subscription_date.

Monthly New Subscribers with Year-over-Year Comparison

Using the company table below, write a single SQL query that returns monthly counts of new subscribers starting at 2019-06-01, plus year-over-year (same-month prior year) comparisons.

Definitions and requirements:

• A "new subscriber" is any company with a non-null subscription_date whose first (and only) subscription_date falls in that month.
• Assume one row per company_id, but your query should still use the earliest subscription_date per company_id in case of data issues.
• The month spine must start at 2019-06-01 and end at the last month that contains any subscription_date.
• Define data_watermark as the maximum subscription_date present in the data.
• A month is partial if its last calendar day is greater than data_watermark.
• Use subscription_date in UTC and explicitly truncate to month boundaries when grouping (e.g., first day of the month).
• You must generate a proper continuous month spine (no gaps) and join monthly counts to it; do not rely on only the existing months in the data.
• Perform a 12-month lag-style join to compare each month with the same month in the prior year (e.g., 2020-06 vs 2019-06).
• Include months that have zero new subscribers.
• Handle division-by-zero safely and ensure months with no data still appear with zeros.
• Do not hardcode the end date; use the data_watermark to determine the final month.

Return the following columns:

• month_start (DATE): the first day of the month.
• new_subscribers (INT): number of new subscribers in that month.
• prior_year_new_subscribers (INT): number of new subscribers in the same month one year earlier (0 if none).
• yoy_abs_delta (INT): new_subscribers minus prior_year_new_subscribers.
• yoy_pct_delta (DECIMAL with 2 decimals): ((new - prior) / prior) * 100, rounded to 2 decimals; NULL if prior_year_new_subscribers is 0.
• is_partial_month (BOOLEAN): TRUE if the month is partial given the data_watermark, FALSE otherwise.

Write the query in a SQL dialect that supports generate_series and date_trunc (e.g., PostgreSQL).

Table company

column	type	constraints
company_id	INT	PRIMARY KEY
signup_date	DATE	NOT NULL
subscription_date	DATE
termination_date	DATE

Sample data:

company_id	signup_date	subscription_date	termination_date
1	2019-05-20	2019-06-02	2020-01-10
2	2019-06-15	2019-06-20	NULL
3	2019-07-01	2019-07-05	2019-12-31
4	2020-06-10	2020-06-11	NULL
5	2020-06-30	2020-07-02	NULL
6	2020-07-15	NULL	NULL

Expected output

One row per month from 2019-06-01 through the last month containing any subscription_date (2020-07-01), including months with zero new subscribers. For each month, show new subscriber counts, prior-year counts, year-over-year absolute and percentage deltas, and whether the month is partial based on the max subscription_date (data_watermark = 2020-07-02).

is_partial_month	month_start	new_subscribers	prior_year_new_subscribers	yoy_abs_delta	yoy_pct_delta
false	2019-06-01	2	0	2	NULL
false	2019-07-01	1	0	1	NULL
false	2019-08-01	0	0	0	NULL
false	2019-09-01	0	0	0	NULL
false	2019-10-01	0	0	0	NULL
false	2019-11-01	0	0	0	NULL
false	2019-12-01	0	0	0	NULL
false	2020-01-01	0	0	0	NULL
false	2020-02-01	0	0	0	NULL
false	2020-03-01	0	0	0	NULL
false	2020-04-01	0	0	0	NULL
false	2020-05-01	0	0	0	NULL
false	2020-06-01	1	2	-1	-50
true	2020-07-01	1	1	0	0

Hints

1. First aggregate new subscriber counts by truncating subscription_date to the month and counting companies whose subscription_date equals their earliest subscription_date.
2. Use generate_series from 2019-06-01 to the month-truncated max(subscription_date) to build a month spine, left join counts to it, then self-join the monthly series offset by 1 year for the prior-year comparison.