SQL Churn and Window Functions | Dataford Interview Questions

Natera wants a monthly view of user churn for a registration-based service. A user is considered active at the beginning of a month when their registration started on or before the first day and has not ended before that day. A churn event occurs when a valid registration end date falls within the month.

Write a single PostgreSQL query using the supplied month calendar and registration dates.

Requirements

Return every month in calendar_months, including months with no churn events.
Calculate users active at the start of each month and users who churned during that month.
Calculate churn_percentage as churned users divided by users active at the start of the month, multiplied by 100, rounded to two decimal places.
Exclude same-day registrations where start_date = end_date from churn counts, and return 0 when the denominator is zero.

Schema

calendar_months

Column	Type	Description
month_startPK	DATE	First day of a reporting month

user_registrations

Column	Type	Description
registration_idPK	INT	Unique registration identifier
user_id	INT	Natera user identifier
start_date	DATE	Date the registration became active
end_date	DATE	Date the registration ended, or NULL when ongoing

Your question is SQL Churn and Window Functions. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem