Compute paid subscriber YoY counts by month

Write a single SQL query (PostgreSQL) that returns calendar-month counts of new paid subscribers starting from 2019-06-01 (inclusive) through the latest month present in the data, along with the same-month prior-year count, absolute YoY change, and YoY percent change. Define a “new paid subscriber” as a company with a non-null subscriptiondate falling in that month. Exclude companies that ever received a free subscription by anti-joining to Free_Subs. Treat dates as UTC and months as calendar months based on subscriptiondate. Include months with zero paid subscribers. Output columns: month_start (DATE, first day of month), new_paid_subscribers (INT), prior_year_new_paid_subscribers (INT), yoy_abs_change (INT), yoy_pct_change (NUMERIC, round to 1 decimal; null if prior year is 0 or null). Assume large tables; avoid scanning entire ranges unnecessarily. Schema and small sample data:

company
+-----------+------------+-----------------+----------------+
| companyid | signupdate | subscriptiondate| terminationdate|
+-----------+------------+-----------------+----------------+
| 1 | 2019-05-20 | 2019-06-01 | 2020-04-15 |
| 2 | 2019-06-15 | 2019-06-30 | NULL |
| 3 | 2019-07-02 | 2019-07-05 | 2019-09-01 |
| 4 | 2020-06-10 | 2020-06-12 | NULL |
| 5 | 2020-06-20 | NULL | NULL |
| 6 | 2018-12-31 | 2019-06-10 | 2019-07-01 |
| 7 | 2019-06-01 | 2019-06-01 | NULL |
| 8 | 2020-06-01 | 2020-06-30 | 2020-07-15 |
| 9 | 2019-06-20 | 2019-07-01 | NULL |
| 10 | 2019-05-01 | 2019-06-01 | NULL |
+-----------+------------+-----------------+----------------+
Free_Subs
+-----------+
| companyid |
+-----------+
| 3 |
| 7 |
+-----------+

Requirements/edge cases:

• Count each company at most once via its single subscriptiondate; ignore rows with NULL subscriptiondate.
• Exclude any companyid found in Free_Subs from all months.
• Generate a month series from 2019-06-01 to max(date_trunc('month', subscriptiondate)) to show months with zero counts.
• The YoY comparison is month vs. the same calendar month 12 months earlier; if no prior-year month exists, set prior_year_new_paid_subscribers and YoY deltas to NULL.
• Add brief notes on indexes you would create to make this performant (no need to actually create them in SQL).

Compute paid subscriber YoY counts by month

You are given two tables in PostgreSQL: company and free_subs.

Write a SINGLE SQL query that returns calendar-month counts of new paid subscribers starting from 2019-06-01 (inclusive) through the latest month present in the paid subscription data.

Definitions / rules:

• A “new paid subscriber” is a company with a non-null subscriptiondate that falls within that calendar month (UTC; treat the DATE values as already in UTC).
• Exclude any company that ever received a free subscription by anti-joining to free_subs (i.e., if companyid exists in free_subs, exclude it from ALL months).
• Each company is counted at most once because there is only one subscriptiondate per company; ignore rows with subscriptiondate IS NULL.
• Generate a month series from 2019-06-01 to max(date_trunc('month', subscriptiondate)) (based on the paid subscription rows you are counting) so that months with zero paid subscribers still appear.
• Add year-over-year (YoY) metrics by comparing each month to the same calendar month 12 months earlier.
  • If the prior-year month does not exist in the generated series, set prior_year_new_paid_subscribers, yoy_abs_change, and yoy_pct_change to NULL.
  • yoy_abs_change = current - prior.
  • yoy_pct_change = ((current - prior) * 100.0 / prior), rounded to 1 decimal; NULL if prior is 0 or NULL.

Output columns:

• month_start (DATE; first day of the month)
• new_paid_subscribers (INT)
• prior_year_new_paid_subscribers (INT)
• yoy_abs_change (INT)
• yoy_pct_change (NUMERIC)

Performance note: assume large tables; avoid scanning unnecessary date ranges.

(Optionally, include brief index suggestions as SQL comments.)

Table company

column	type	constraints
companyid	INT	PRIMARY KEY, NOT NULL
signupdate	DATE
subscriptiondate	DATE
terminationdate	DATE

Sample data:

companyid	signupdate	subscriptiondate	terminationdate
1	2019-05-20	2019-06-01	2020-04-15
2	2019-06-15	2019-06-30	NULL
3	2019-07-02	2019-07-05	2019-09-01
4	2020-06-10	2020-06-12	NULL
5	2020-06-20	NULL	NULL
6	2018-12-31	2019-06-10	2019-07-01
7	2019-06-01	2019-06-01	NULL
8	2020-06-01	2020-06-30	2020-07-15
9	2019-06-20	2019-07-01	NULL
10	2019-05-01	2019-06-01	NULL

Table free_subs

column	type	constraints
companyid	INT	PRIMARY KEY, NOT NULL

Sample data:

companyid
3
7

Expected output

One row per calendar month from 2019-06-01 through 2020-06-01 (the latest month with a paid subscriptiondate in the sample), including months with zero counts. Prior-year fields are NULL when the prior-year month is outside the generated series. YoY percent is NULL when prior year is NULL or 0.

month_start	new_paid_subscribers	prior_year_new_paid_subscribers	yoy_abs_change	yoy_pct_change
2019-06-01	4	NULL	NULL	NULL
2019-07-01	1	NULL	NULL	NULL
2019-08-01	0	NULL	NULL	NULL
2019-09-01	0	NULL	NULL	NULL
2019-10-01	0	NULL	NULL	NULL
2019-11-01	0	NULL	NULL	NULL
2019-12-01	0	NULL	NULL	NULL
2020-01-01	0	NULL	NULL	NULL
2020-02-01	0	NULL	NULL	NULL
2020-03-01	0	NULL	NULL	NULL
2020-04-01	0	NULL	NULL	NULL
2020-05-01	0	NULL	NULL	NULL
2020-06-01	2	4	-2	-50

Hints

1. Use generate_series(start_date, max_month, interval '1 month') to include months with zero counts.
2. Compute monthly counts first, then self-join on month_start - interval '1 year' to get prior-year values.
3. Use an anti-join (LEFT JOIN ... WHERE free_subs.companyid IS NULL) to exclude free subscribers from all months.