Exclude free subscribers via anti-join

Extend your previous monthly new-subscriber + YoY query to exclude companies that ever received a free subscription, using an anti-join against Free_Subs. Ensure excluded companies are removed from all months and all derived YoY metrics. Your solution should be resilient to duplicates in Free_Subs and should not accidentally re-include rows due to joins. Also, briefly explain in comments the trade-offs between using NOT EXISTS versus LEFT JOIN ... IS NULL for this anti-join under typical OLAP columnar engines.

Additional table and sample data:

Free_Subs
+------------+
| company_id |
+------------+
| 2 |
| 5 |
+------------+

Requirements:

• Do not change the output column names/types from the first question.
• The anti-join must occur before aggregation so excluded IDs do not affect zero-filled months or YoY baselines.
• Guard against duplicate company_id values in Free_Subs (e.g., use DISTINCT or NOT EXISTS).

Monthly new subscribers with YoY metrics excluding free subscribers via anti-join

You work with a subscriptions fact table and a small months calendar. A "new subscriber" is defined as a company on the month of its first paid subscription (based on the earliest start_date in Subscriptions for that company).

Using the tables below, write a query that:

1. Computes monthly new-subscriber counts.
2. Zero-fills months using Month_Calendar so that every month in the calendar appears, even if there were no new subscribers.
3. Adds year-over-year (YoY) metrics by comparing each month to the same calendar month in the previous year.
4. Excludes any company that ever appears in Free_Subs from all months and all derived YoY metrics. The anti-join must occur before calculating first-subscription dates and monthly aggregations, so that excluded companies do not affect zero-filled months or YoY baselines.
5. Is resilient to duplicate company_id values in Free_Subs (i.e., your anti-join logic must not break if duplicates are present).

The final output must have exactly these columns and types:

• month_start (DATE) – first day of the month from Month_Calendar
• year (INT)
• month (INT) – numeric month (1–12)
• new_subscribers (INT) – count of companies whose first paid subscription falls in that calendar month, excluding companies in Free_Subs
• prev_year_new_subscribers (INT) – new_subscribers for the same month in the previous year (0 if that prior month is not present or had no new subscribers)
• yoy_abs_change (INT) – new_subscribers - prev_year_new_subscribers
• yoy_pct_change (DECIMAL(10,2)) – (new_subscribers - prev_year_new_subscribers) / prev_year_new_subscribers * 100; return NULL when prev_year_new_subscribers = 0 to avoid division by zero.

Additionally, in SQL comments within your query, briefly explain the trade-offs between using NOT EXISTS versus LEFT JOIN ... IS NULL for this anti-join under typical OLAP columnar engines (e.g., Snowflake, BigQuery, Redshift).

Table Subscriptions

column	type	constraints
subscription_id	INT	PRIMARY KEY, NOT NULL
company_id	INT	NOT NULL
start_date	DATE	NOT NULL

Sample data:

company_id	start_date	subscription_id
1	2023-01-15	1
1	2023-03-10	2
2	2023-02-01	3
3	2023-03-20	4
4	2024-01-05	5
5	2024-02-10	6
6	2024-02-25	7
7	2024-03-15	8

Table Free_Subs

column	type	constraints
company_id	INT	NOT NULL

Sample data:

company_id
2
5

Table Month_Calendar

column	type	constraints
month_start	DATE	PRIMARY KEY, NOT NULL
year	INT	NOT NULL
month	INT	NOT NULL

Sample data:

month	month_start	year
1	2023-01-01	2023
2	2023-02-01	2023
3	2023-03-01	2023
1	2024-01-01	2024
2	2024-02-01	2024
3	2024-03-01	2024

Expected output

One row per month in Month_Calendar, with monthly new-subscriber counts and YoY metrics, after excluding any company that appears in Free_Subs from all calculations.

month	month_start	new_subscribers	prev_year_new_subscribers	year	yoy_abs_change	yoy_pct_change
1	2023-01-01	1	0	2023	1	NULL
2	2023-02-01	0	0	2023	0	NULL
3	2023-03-01	1	0	2023	1	NULL
1	2024-01-01	1	1	2024	0	0
2	2024-02-01	1	0	2024	1	NULL
3	2024-03-01	1	1	2024	0	0

Hints

1. First compute each company’s first paid subscription date, and apply the anti-join against Free_Subs in that step before aggregating.
2. Use DATE_TRUNC('month', first_paid_date) and a LEFT JOIN to Month_Calendar to ensure every month appears with zeroes where appropriate.
3. Compute prev_year_new_subscribers by self-joining the monthly result to itself with year-1, then derive YoY difference and percentage.
4. Use NOT EXISTS (or DISTINCT in a subquery) so that duplicates in Free_Subs do not affect counts.