Build 30-day retention cohort table

Using the same schema, produce a monthly cohort table of 30-day retention based on signupdate. For each cohort_month = date_trunc('month', signupdate), compute cohort_size, d30_retained, and d30_retention_rate = d30_retained/cohort_size. A company is considered retained at day 30 if terminationdate is NULL or terminationdate >= signupdate + INTERVAL '30 days' (i.e., churn strictly before day 30 is not retained; churn on day 30 counts as retained). Handle right-censoring by excluding signups where signupdate + INTERVAL '30 days' > as_of_date. Use as_of_date = '2025-09-01'. Return: cohort_month (DATE, first day of month), cohort_size (INT), d30_retained (INT), d30_retention_rate (NUMERIC, 3 decimals). Do not exclude Free_Subs in this metric unless you add a separate column showing paid-only retention. Schema and small sample data: company +-----------+------------+-----------------+----------------+ | companyid | signupdate | subscriptiondate| terminationdate| +-----------+------------+-----------------+----------------+ | 101 | 2019-06-05 | 2019-06-06 | 2019-06-25 | -- churn < 30d (not retained) | 102 | 2019-06-10 | 2019-06-15 | NULL | -- retained | 103 | 2019-06-20 | NULL | NULL | -- retained (no termination) | 104 | 2019-07-01 | 2019-07-02 | 2019-07-31 | -- churn at 30d (retained) | 105 | 2019-07-15 | 2019-07-16 | 2019-07-10 | -- churn before signup? (data quality) | 106 | 2025-08-20 | 2025-08-21 | NULL | -- censored; exclude from 30d | 107 | 2020-01-01 | 2020-01-02 | 2020-02-15 | -- retained | 108 | 2020-01-10 | NULL | 2020-01-25 | -- churn < 30d (not retained) +-----------+------------+-----------------+----------------+ Questions to address in your solution: - Provide SQL (PostgreSQL) that calculates the cohort table with the censoring rule and the day-30 boundary condition as defined. - Show how you would optionally add paid-only retention by excluding companyids in Free_Subs via an additional column paid_d30_retention_rate. - Explain in comments how you would handle obvious data-quality anomalies (e.g., terminationdate < signupdate).