Build 30-day retention cohort table
Monthly 30-Day Retention Cohort (PostgreSQL)
You are given a table of companies with signup and (optional) termination dates. Define monthly cohorts by the signup month and compute 30-day retention per cohort. Use a fixed as_of_date to handle right-censoring.
Retention Rule: A company is retained at day 30 if terminationdate is NULL or terminationdate is greater than or equal to signupdate + 30 days. Churn strictly before day 30 is not retained; churn on day 30 counts as retained.
Right-Censoring: Exclude any signup whose 30-day window has not fully elapsed by as_of_date. This means excluding signups where signupdate + 30 days > as_of_date. The fixed as_of_date is '2025-09-01'.
Base Metric: Do not exclude Free_Subs from the base metric. If a paid-only retention metric is desired, add an additional column that excludes any companyid present in the Free_Subs table.
Schema:
Table: company
companyid(INT)signupdate(DATE)subscriptiondate(DATE)terminationdate(DATE)
Task:
Produce a monthly cohort table of 30-day retention based on signupdate.
cohort_month: The first day of the month ofsignupdate.cohort_size: The number of companies in the cohort (after right-censoring and any data-quality handling).d30_retained: The number of companies retained at day 30.d30_retention_rate:d30_retained/cohort_size(formatted to 3 decimal places).
Output Columns:
cohort_month(DATE)cohort_size(INT)d30_retained(INT)d30_retention_rate(NUMERIC)
Additional Requirement: In comments within your SQL query, explain how to handle data-quality anomalies such as terminationdate < signupdate.
AI interview prep
Core dilemma
Accurately calculating retention over time while correctly handling data censoring and potential data quality issues within a large dataset.
3 tradeoffs and 2 stress tests locked
Interview prep
Unlock the interviewer X-Ray rubric, tradeoff matrix, stress tests, clarifying questions, and key concepts.
Build 30-day retention cohort table
Monthly 30-Day Retention Cohort (PostgreSQL)
You are given a table of companies with signup and (optional) termination dates. Define monthly cohorts by the signup month and compute 30-day retention per cohort. Use a fixed as_of_date to handle right-censoring.
Retention Rule: A company is retained at day 30 if terminationdate is NULL or terminationdate is greater than or equal to signupdate + 30 days. Churn strictly before day 30 is not retained; churn on day 30 counts as retained.
Right-Censoring: Exclude any signup whose 30-day window has not fully elapsed by as_of_date. This means excluding signups where signupdate + 30 days > as_of_date. The fixed as_of_date is '2025-09-01'.
Base Metric: Do not exclude Free_Subs from the base metric. If a paid-only retention metric is desired, add an additional column that excludes any companyid present in the Free_Subs table.
Schema:
Table: company
companyid(INT)signupdate(DATE)subscriptiondate(DATE)terminationdate(DATE)
Task:
Produce a monthly cohort table of 30-day retention based on signupdate.
cohort_month: The first day of the month ofsignupdate.cohort_size: The number of companies in the cohort (after right-censoring and any data-quality handling).d30_retained: The number of companies retained at day 30.d30_retention_rate:d30_retained/cohort_size(formatted to 3 decimal places).
Output Columns:
cohort_month(DATE)cohort_size(INT)d30_retained(INT)d30_retention_rate(NUMERIC)
Additional Requirement: In comments within your SQL query, explain how to handle data-quality anomalies such as terminationdate < signupdate.